Google Answers Logo
View Question
Q: Excel PResent value question ( Answered 5 out of 5 stars,   3 Comments )
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

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:5 out of 5 stars

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.


"Loan Payment Formula":

"Loan or Investment Formulas":

"Mortgage monthly payment":


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:

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,

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.


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


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

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):

The upgraded spreadsheet is:

I hope this helps you.

Best regards,
bischofflaw-ga rated this answer:5 out of 5 stars and gave an additional tip of: $20.00
thanks so much.  I may need some later claification.

Subject: Re: Excel PResent value question
From: reinedd-ga 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: coolanchi-ga 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: bischofflaw-ga 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.


Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy