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