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

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

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

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)
``````

• 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 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