excelexcel-formulafinance

Is there an Excel function to calculate the future value of constant interest rate, variable payments?


So, let's say I have a series of ten monthly payments, where each payment is 25% greater than the previous; on the other hand, I have a constant monthly interest rate of 3%.

Knowing that the first payment should be of $220,000 COP, the payments will look like this:

NPER Payment
0
1 $ 220.000,00
2 $ 275.000,00
3 $ 343.750,00
4 $ 429.687,50
5 $ 537.109,38
6 $ 671.386,72
7 $ 839.233,40
8 $ 1.049.041,75
9 $ 1.311.302,19
10 $ 1.639.127,73

I know that, given constant payments AND a constant interest rate, I could user FV, or given a present value with variable interest rates I could use FVSCHEDULE. Is there any way to calculate it in a shorter manner?

Apologies for my English.

To calculate it, I used =FV(AZ5;AZ7;;-NPV(AZ5;BC6:BC15)), where AZ5 is the interest rate of 3%, AZ7 is the 10 NPER, and, withing NPV, AZ5 is the interest rate and BC6:BC15 are the ten payments. This, currently, gets the work done; however, I wanted to if there any alternative methods to calculate avoiding to calculate NPV first.


Solution

  • I think you can use below:

    =SUMPRODUCT(220000*POWER(1+0.25,SEQUENCE(10,1,0,1)),POWER(1+0.03,SEQUENCE(10,1,9,-1)))
    

    First, you can use below to generate 10 monthly payments, each payment is 25% greater than the previous,

    =InitialAmount*POWER(1+pct,SEQUENCE(numPayments,1,0,1))
    

    where pct = 0.25, numPayments = 10, InitialAmount = 220000. (the payment in the first month)

    Then you need a formula to compound interest rate monthly:

    =POWER(1+rate,SEQUENCE(numPayments,1,numPayments-1,-1))
    

    where rate = 0.03, numPayments = 10

    Final step is to use SUMPRODUCTto sum monthly payments.

    I have tested it, if you set pct = 0, that is, monthly payments remain constant, below formula will return the exact same result as =FV(0.03,10,220000,0,0)

    =SUMPRODUCT(220000*POWER(1+0,SEQUENCE(10,1,0,1)),POWER(1+0.03,SEQUENCE(10,1,9,-1)))