View Question
 Question
 Subject: Excel PResent value question Category: Business and Money > Finance Asked by: bischofflaw-ga List Price: \$20.00 Posted: 04 Oct 2006 10:45 PDT Expires: 03 Nov 2006 09:45 PST Question ID: 770775
 ```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.```
 Subject: Re: Excel PResent value question Answered By: livioflores-ga on 11 Oct 2006 08:01 PDT Rated:
 ```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```
 bischofflaw-ga rated this answer: and gave an additional tip of: \$20.00 `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```