How do I calculate a payment?

  • Thread starter Thread starter aidesifuentes
  • Start date Start date
A

aidesifuentes

I have a starting investment and I have the future value that I want to
attain over a specified period. Which financial function will calculate the
payments I need to make over the specified period to reach my future value
goal?
 
Actually I've looked at the various payment functions and they seem to be
calculating unrealistic payments. Using the PMT function and the variables
below, I got a monthly payment of $35k over five years to reach $1.8million
but I started with $500k so a monthly payment of $35k seems much too high
when 60 payments of $35k will accumulate over $2million alone without the
starting $500k. What am I doing wrong?

Payment ($35,137.74)

Rate 0.83%
Periods 60
PV $500,000.00
FV $1,898,309.51
Type 0

60 monthly payments at $35k/month = $2,108,264.61
 
Use the PPMT formula,
Where your rate = 9.96%/12 = 0.83%
Per =1
Nper=60
PV =0
FV=1800000
Type=1
This will give you a payment of $23,077.72 which equals $1384663 plus the
500000 you have equals $1800000
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.
 
Actually you have to calculate the return on the $500K investment:
=FV(0.096/12,60,0,500000,0)*-1
which equals:$821,024.35
Now you only need payments to achieve (1800000-821024.35) which equals to
$978,975.65; so this is the basis for your monthly payments in the formula:
=PPMT(0.0096/12,1,60,0,978975.65,1)
Which returns a more reasonable monthly deposit or payment of $12,551.40
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.
 
So the key is to use two formulas rather than one which is what I was trying
to do.
When using these payment functions, do you supply FV or PV but not both?

Thanks for your help!!

Barbara
 
Turns out PMT or PPMT will work. I was trying to do the whole calculation in
one formula when it really required two. Thanks for your help!
 
You only need one function, PMT, as in:

=PMT(10%/12,5*12,500000,-1898309.51)
=13,775.90

Regards,
Fred.
 
Back
Top