I am trying to calculate the difference in present value of a loan
calculated at 4.5% and 5.5%.  I want to do the calculations monthly on
each loan payment.

My loan amount is 232,023.00 with monthly payment at 5.5% of 1596.10
for 240 months.

I have created an excel table with the different interest payments per
month and have calculated the difference.

For example, the month of hte payment is in Column B (B62 would be
10/09) and the difference in interest payments is Column K (k62 for
10.09).

My interest rate assumption is 3.273.

Thanks for your help.



The lower interest rate would be 4.5% for a monthly payment of 1467.89

---

Request for Question Clarification by livioflores-ga on 05 Oct 2006 07:17 PDT

Can you post the xls file you have to see what do you need, it is not
so clear to me.

Thank you.

---

Clarification of Question by bischofflaw-ga on 09 Oct 2006 08:03 PDT

I will try to claify.  Client was made a loan in August 2004 at 5.5. 
I think the loan should have been at 4.5.

I am trying to calculate the present value of the difference in the loan payments.

For example, in November, 2009 what is the present value of the
differeence between the interest paid for that month for the 4.5
percent loan and the 5.5 percent loan.

Hi!!


The monthly payment for the 5.5% loan is $1596.06 and the monthly
payment for the 4.5% loan is $1467.89; this means that each month from
the first to the 240th you will pay an extra amount of ($1596.06 -
$1467.89) = $128.16

You want to know the present value of this extra payment considering
an opportunity cost of 3.273%

Note that each extra payment can be discounted to its correspondent
present value, for example the present value (PV) of the n_th extra
payment is:

PV_n = PMT/(1+R/12)^n = $128.16/(1+(0.03273/12))^n

To know the present value of the sum of all payments from now to the
month n you just need to add up all the present values from now to
such month. Affortunatelly there is one formula to do that:

PV = PMT * [1 - (1+R/12)^(-n)] / (R/12)


For further readings about the about formulas and concepts see:
"Other Compounding Periods":
See the Present Value of an Annuity section.
http://www2.hpu.edu/mlane/BusinessFinanceOnline/TVM/OtherCompounding.html

"Annuities":
http://www.netmba.com/finance/time-value/annuity/

"Loan Payment Formula":
http://www.1728.com/loanform.htm

"Loan or Investment Formulas":
http://oakroadsystems.com/math/loan.htm

"Mortgage monthly payment calculator-Interest.com":
http://mortgages.interest.com/content/calculators/monthly-payment.asp

 

The result of this problem is very impresive, the present value of all
the extra payments (or if you prefer the difference in payments) after
the last payment (month 240) is $22,549.79

I have prepared an Excel spreadsheet so you can see the evolution of
the PVs of the extra amount for paying a more expensive loan, you can
download it from here:
http://www.geocities.com/artistaflores/Present_value_question.xls


Search strategy:
loan payment formula
annuity "present value" formula
formula "cash flow" "present value" monthly


I hope you find this answer useful. Please feel free to request for a
clarification if you find something unclear before rate this answer, I
will be glad to give you further assistance on this question if you
need it.

Best regards,
livioflores-ga

---

Clarification of Answer by livioflores-ga on 18 Oct 2006 20:59 PDT

Thank you so much for the good rating and the generous tip.
Do not hesitate to use the clarification feature at any time in the
future if you need it.

Regards,
livioflores-ga

---

Request for Answer Clarification by bischofflaw-ga on 24 Oct 2006 17:53 PDT

Can you set it up so that I can enter the variables myself?

I use different number of years, principle, etc?

Thanks Richard

Bischofflaw  gmail.com

---

Clarification of Answer by livioflores-ga on 24 Oct 2006 21:03 PDT

Hi!!

In the original spreadsheet you can change everything but the
inflation rate that you have fixed at 3.273%, for everything I mean:
-Loan Amount
-Years --> number of payments = Years * 12
-Interest rates (lower and higher)

Now I have modified the spreadsheet so you can also change the
inflation rate (cell F2). All values that you can change are in RED.
NOTE: a more appropiate rate used for discounting the difference in
payments is the risk free rate , you can use for this value the
10-YEAR TREASURY NOTE (^TNX), the current value is 4.82% (just input
thisvalue on the F2 cell).
For updates see its Yahoo! Finance page (search for Index Value):
http://finance.yahoo.com/q?s=%5ETNX


The upgraded spreadsheet is:
http://www.geocities.com/artistaflores/Present_value_question_2.xls


I hope this helps you.

Best regards,
livioflores-ga

thanks so much.  I may need some later claification.

I may be mighty stupid but I don't get it.

The present value of a 232,023.00 loan is always 232,023.00 for every
interest rate.

What are you looking for exactly??

I agree. PV remains same irrespective of interest rate since discount
rate is same as interest rate.

I think the requestor may be looking to find PV of extra interest
paid. (4.5 vs 5.5). For this one needs to know opportunity cost of the
extra money paid.that can act as discount rate

Coolanchi has it right.  I have calculated the difference in the
interest paid as opposed to what was represented.  i.e.  for March
2009, the consumer paid 49.00 more than they should have.

Assuming a opportunity cost of 3.23% (inflation rate) what is the
present value of that 49.00 paid in March 2009.

I don't know how to post an excel spread sheet here.

Thanks