excelexcel-formulaformularate

Using Excel RATE function to solve the present value

I would like to use future value (FV), Payment terms (PMT), Balloon payment months (NPER#1), Amortization months (NPER#2) to calculate the present value of the loan.

Say the figures are as follows:
FV = $168,431.47
PMT = 7,276.70
NPER#1 = 36 months
NPER#2 = 60 months.

On the ground that the rate calculated as follows should be the same:

**Rate(NPER#2,PMT,-PV,0,0) = Rate(NPER#1, PMT,-PV,FV,0)**

The equilibrium result should be 0.292% of rate and 400,000 of PV.

I listed out most if not all excel formula regarding time value, e.g. rate, pmt, ipmt, nper, together with the online balloon loan calculator and tried to understand the correlation. However, no luck for the past 5 days/over 40 hours.

Any idea for how to solve the above equation in order to calculate the present value of the loan would be much appreciated.

Thank you so much!

Solution

  • Set the spreadsheet will a cell for each variable including PV and a formula for the difference between the two rates like so.

    enter image description here

    Then use the Solver add-in, setting the objective B9 to 0 and changing cell B1. I tried Goal Seek but that only got close.

    EDIT:

    Why not just use RATE(NPER#2-NPER#1,PMT,FV,0,0) to get the rate and then PV(RATE,60,PMT) to get PV.