excel spreadsheet current version, formula for payment remainders from current date

Question

I want a formula to do the following: work out how many payments are needed after a date has happened so for instance, purchased 20/6/2016 & payment ends 20/6/2020 with payments to be made monthly. how do you get the formula to populate once logging into the spreadsheet to show how many repayments are left ie 13 or 16? much appreciated

Answer 1

C2=12*(YEAR(B2)-YEAR(A2))+MONTH(B2)-MONTH(A2)-(DAY(B2)<DAY(A2))

Answer 2

You can use payment end date and substract it from the date of today. This outputs the number of days between the two dates. To get the date of today use the function TODAY().

Then divide by 365.25 and multiply by 12 to get the amount of months between the two dates. Then use rounding to get the payments that still need to be done.

=ROUNDDOWN((B2-TODAY())/365.25*12,0) 

Answer 3

I would like to suggest method, calculates number of payments remaining, after check if mode of payment is either Monthly or Yearly.

Additionally, I'm suggesting how to find date of payments between Purchase and last payment Date.

N.B.

The purpose behind enhancing the scope of calculation is, to take on professional approach & make the method more competent.

How it works:

• Enter mode of payment in Cell B271.
• Write installment paid, if any in Cell D271.

• Formula in Cell E271:

  =IF(B271="Monthly",(DATEDIF(A271,C271,"M")-D271),IF(B271="Yearly",(DATEDIF(A271,C271,"Y")-D271),""))
  

Generate number of installments in Cell A274 onwards.

• Put 1 in Cell A274.

• Formula in Cell B274 & fill till required.

=IF(A$274>=48,"",A274+1)

• Formula in Cell A275 & fill Right.

  =IF(ISBLANK(A$274),"",DATE(YEAR($A$271),MONTH($A$271)+A$274,DAY($A$271)))

• Formula for yearly installment Dates:

  =IF(ISBLANK(A$274),"",DATE(YEAR($A$271)+A$274,MONTH($A$271),DAY($A$271)))

Adjust cell references in the formula as needed