Forum Discussion
"Reverse FV" function? Or?
Hi all, I need some help please.
I would like formula for excel, where I have known inputs:
A1 = 30 (number of years)
A2 = 500000 (needed capital)
A3 = 8% (known yearly return of investment p.a.)
A4 = 10000 (initial investment)
and I need to calculate needed monthly investment, to reach that required sum (in A2).
Thank you very much for your help and time.
Daniel
7 Replies
Thank you both very much! You are great 👍
If you're looking for an alternative way to calculate the required monthly investment (not using PMT), you can also derive it using the future value of a series formula and solve it algebraically.
= (A2 - A4 * (1 + A3/12)^(A1*12)) / (((1 + A3/12)^(A1*12) - 1) / (A3/12))
This is mathematically identical to PMT, but avoids using built-in financial functions — useful if:
You want to understand what's happening behind the scenes.
You’re building a custom financial model.
You're using software without PMT.
...otherwise, for the sake of simplicity, I recommend the formula suggested by Mr. Vogelaar 🙂.
Thank you very much Nikolino!
Can you recommend me also algebraical function as a replacement of POWER function from Excel?
I mean:
A1 = 100% (total return)
A2 = 10 (years of investment)
A3 = needed yearly return (p.a.) which I need to extract
Now I am using POWER function inside Excel, but if there is algebraical formula for this, it will be much appreciated 😁
Thank you!
That is the PMT function:
=PMT(A3/12, 12*A1, -A2, A4)
(The 12 converts from years to months)
See PMT function
Hello Hans,
thank you for your reply. I have tried it with that exact numbers from my example, but it returns wrong number (4267,78). Also when I enter initial investment to zero (0 to A4), result only change to 4274,49.
What am I doing wrong please?
Thank you! D.
Sorry, my bad - I switched two arguments.
=PMT(A3/12, 12*A1, A4, -A2)
