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
3 Answers
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((B2TODAY())/365.25*12,0)

hi thanks very much. this formula works well but how do I use rounding to get the payments to still be done? Commented Nov 18, 2019 at 1:09
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 CellA274
.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