

Subject:
Excel PResent value question
Category: Business and Money > Finance Asked by: bischofflawga 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  
 


Subject:
Re: Excel PResent value question
Answered By: liviofloresga 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/timevalue/annuity/ "Loan Payment Formula": http://www.1728.com/loanform.htm "Loan or Investment Formulas": http://oakroadsystems.com/math/loan.htm "Mortgage monthly payment calculatorInterest.com": http://mortgages.interest.com/content/calculators/monthlypayment.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, liviofloresga  
 
 

bischofflawga
rated this answer:
and gave an additional tip of:
$20.00
thanks so much. I may need some later claification. 

Subject:
Re: Excel PResent value question
From: reineddga on 05 Oct 2006 05:45 PDT 
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?? 
Subject:
Re: Excel PResent value question
From: coolanchiga on 07 Oct 2006 05:09 PDT 
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 
Subject:
Re: Excel PResent value question
From: bischofflawga on 08 Oct 2006 21:28 PDT 
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 
If you feel that you have found inappropriate content, please let us know by emailing us at answerssupport@google.com with the question ID listed above. Thank you. 
Search Google Answers for 
Google Home  Answers FAQ  Terms of Service  Privacy Policy 