Using Excel RATE function to solve the present value

[lordy888]

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.

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!

 

[JeffM]

I don't quite understand your problem, and you probably would have got a quicker response had you posted in finance/business math, but no problem.

In any case, if the future value is under 170k, then the present value cannot be greater than the future value. Let's do the math first.

What has me confused is a balloon payment paid over months. On the assumption that what you mean is that payments are due at the end of n successive months and a single balloon payment is due at the end of month n + 1, we have this equation

[MATH]f = (1 + r)^{(n+1)} \cdot p =[/MATH]
[MATH](1 + r)^{(n+1)} \cdot \left \{ a \cdot \displaystyle \frac{1 - \displaystyle \frac{1}{(1 - r)^n}}{r} + \displaystyle \frac{b}{(1 + r)^{(n+1}} \right \}, \text { where }[/MATH]
[MATH]f = \text {future value;}[/MATH]
[MATH]p = \text {present value;}[/MATH]
[MATH]a = \text {monthly amortization;}[/MATH]
[MATH]r = \text {MONTHLY interest rate expressed as a decimal; and}[/MATH]
[MATH]b = \text {balloon payment.}[/MATH]
That is an ugly equation. It cannot be solved analytically for r. That requires numerical methods, which is a topic not taught to most students.

I am not an expert in excel, and I am not sure I have ever used the RATE function. I have, however, used the IRR function many times to solve this kind of problem.

I am going to go to excel and deal with the specifics of your problem. I shall repost as soon as I have finished my excel exercise. I am sorry not to give you a complete answer in a single post, but (a) I am busy watching my grandson and (b) I really am not sure I even understand your question.

EDIT I see that I did not read your post carefully enough. It is not really a balloon payment; it is a revised amortization amount. That makes the equation even worse. I shall work on what I now see you were saying.

EDIT 2: If my understanding of what you are asking in my previous edit is correct, the problem cannot be solved at all until you tell me what the payments will be in the second set of monthly payments. I am not going to waste my time on excel with a problem that has not been fully specified.

 

[lordy888]

Thank you. Sir. I posted another post in finance/business math and simplified the problem instead.

I am new to the forum. Sorry for any inconvenience.

Way to calculate present value without interest rate

The information would be: Monthly instalment of 12,278 will be paid to settle part of principal and interest. Amount will be fully repaid after 36 months, implying the future value should be 0. Payment is in arrear. Is it possible to calculate the present value with these information. The...

www.freemathhelp.com

.