NPV Discount Rate

F

fcan12

New Member
Joined
Jan 25, 2019
Messages
2
I have been offered 2 payment methods as I was buying some tools for a company I work for. I need your help to assess the best method. Here it goes,

Total cost is $100 and I don't want to pay it all in advance.

First offer is they increase the total cost by 30% (they call it "financing cost" , total cost becomes $130) and make the payment as 24 equal monthly installments. (130/24 ~5,42 USD every month for 24 months).

Second offer is total cost increases by 45% (again as "financing cost", total cost becomes $145) and make the payment as 36 equal monthly installments.

I would like to have yearly discount rate as 20%.

-So how do I calculate the best offer using Npv formula?

I believe firstly I need "monthly discount rate". So here is an additional question. -Is it 20%/12 or similar to effective interest rate calculation, if it is the latter why?

Thanks in advance.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
fcan12 said:
I believe firstly I need "monthly discount rate". [....] Is it 20%/12 or similar to effective interest rate calculation, if it is the latter why?
Click to expand...

This is a finance question, not an Excel question.

Unless this is a class exercise, the answer is: it does not matter for your purpose, as long as you are consistent. The actual value of the NPVs will differ, of course. But the relative order (which one is less) is the same, in either case. So I would use 20%/12, because it is easier to calculate.

(My answer might be different if you were considering options with different payment frequencies -- monthly v. annual, for example. I'd have to give that more thought.)

In fact, there is no agreement in finance texts about whether to use simple or compounded rates. So if this is for a class exercise, you should consult your finance text or instructor.

Some related observations....

It would be tedious to use the Excel NPV function for this purpose. Since the payment is "fixed" (the same for each period), I would use the Excel PV function.

If you use the Excel NPV function and this is for a class exercise, so the actual NPV value is important, pay special attention to how Excel NPV discounts the initial cash flow (100). It is different from the Excel PV function and most finance texts.

In the real world (again, in contrast with a class excercises), the "best" offer is not necessarily determined by the smallest NPV. Monthly cash flow might be the controlling issue. In that case, you might choose the smalller payment, even if the NPV and total interest is more.

Finally, it is unclear to me why you are setting the discount rate. I would be more interested in the (monthly) interest rate. To that end, I would use the Excel RATE function to determine the discount that causes the NPV to be zero (i.e. the IRR).

Again, whether you annualize the monthly rate by multiplying by 12 or by compounding over 12 periods is matter of "taste" or regional/industry conventions.
 
Last edited:
Upvote 0
joeu2004 said:
This is a finance question, not an Excel question.

Unless this is a class exercise, the answer is: it does not matter for your purpose, as long as you are consistent. The actual value of the NPVs will differ, of course. But the relative order (which one is less) is the same, in either case. So I would use 20%/12, because it is easier to calculate.

(My answer might be different if you were considering options with different payment frequencies -- monthly v. annual, for example. I'd have to give that more thought.)

In fact, there is no agreement in finance texts about whether to use simple or compounded rates. So if this is for a class exercise, you should consult your finance text or instructor.

Some related observations....

It would be tedious to use the Excel NPV function for this purpose. Since the payment is "fixed" (the same for each period), I would use the Excel PV function.

If you use the Excel NPV function and this is for a class exercise, so the actual NPV value is important, pay special attention to how Excel NPV discounts the initial cash flow (100). It is different from the Excel PV function and most finance texts.

In the real world (again, in contrast with a class excercises), the "best" offer is not necessarily determined by the smallest NPV. Monthly cash flow might be the controlling issue. In that case, you might choose the smalller payment, even if the NPV and total interest is more.

Finally, it is unclear to me why you are setting the discount rate. I would be more interested in the (monthly) interest rate. To that end, I would use the Excel RATE function to determine the discount that causes the NPV to be zero (i.e. the IRR).

Again, whether you annualize the monthly rate by multiplying by 12 or by compounding over 12 periods is matter of "taste" or regional/industry conventions.
Click to expand...


Thank you for your valuable input.

I would like to clarify something, this is not a class exercise and I would like to be as precise as possible to support the decision maker.

Monthly cash flow is a minor concern in this case and major concern is net present value of each offer.

As for the yearly discount rate, honestly there is a company wide discount rate and I am not in a position to challenge that. However your comment on consistency and relative order makes sense to me.

So it looks like using simple monthly discount rate calculation and picking the lowest NPV works in this case,right?

Out of curiosity, I would like to ask your opinion on discount rate calculation if there are different payment frequencies?
 
Upvote 0
fcan12 said:
As for the yearly discount rate, honestly there is a company wide discount rate
Click to expand...

So, someone in the company finance dept should be able to tell you how they want you to determine sub-annual rates.

fcan12 said:
So it looks like using simple monthly discount rate calculation and picking the lowest NPV works in this case,right?
Click to expand...

If the company specifies an annual discount rate of 20%, I suspect that they might be interested in the actual NPV value, at least sometimes, not just the relative order of NPVs for two or more alternatives.

In that case, I would "de-compound" (discount) the annual rate instead of simply dividing it.

For example, a monthly rate for 20% would be NOMINAL(20%,12)/12 or (1+20%)^(1/12)-1. Thus, the monthly rate compounded over 12 periods is 20% annually.

The following example might help.


[TABLE="class: grid, width: 600"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]Simple Rate[/TD]
[TD="align: right"]PV[/TD]
[TD="align: right"]NPV
[/TD]
[TD="align: right"]Equal?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]pmt#[/TD]
[TD="align: right"][/TD]
[TD="align: right"]24 pmts[/TD]
[TD="align: right"]36 pmts[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]24 pmts[/TD]
[TD="align: right"]$106.43[/TD]
[TD="align: right"]$106.43[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]Jan'19[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]36 pmts[/TD]
[TD="align: right"]$108.38[/TD]
[TD="align: right"]$108.38
[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]Feb'19[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]Mar'19[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]Compounded Rate[/TD]
[TD="align: right"]PV[/TD]
[TD="align: right"]NPV[/TD]
[TD="align: right"]Equal?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]Apr'19[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]24 pmts[/TD]
[TD="align: right"]$108.11[/TD]
[TD="align: right"]$108.11[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]May'19[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]36 pmts[/TD]
[TD="align: right"]$110.84[/TD]
[TD="align: right"]$110.84[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: center"]Jun'19[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: center"]Jul'19[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]Annual Rate[/TD]
[TD="align: right"][/TD]
[TD="align: right"]XNPV[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: center"]Aug'19[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]24 pmts[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$108.13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: center"]Sep'19[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]36 pmts[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$110.85[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: center"]Oct'19[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: center"]Nov'19[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: center"]Dec'19[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]23[/TD]
[TD="align: center"]Dec'20[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]24[/TD]
[TD="align: center"]Jan'21[/TD]
[TD="align: right"]-$5.42[/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: center"]Feb'21[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]37[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]35[/TD]
[TD="align: center"]Dec'21[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
[TR]
[TD="align: center"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]36[/TD]
[TD="align: center"]Jan'22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-$4.03[/TD]
[/TR]
</tbody>[/TABLE]
Code: 
Formulas:
B2:  =PV(20%/12,24,-130/24,0)
C2:  =-NPV(20%/12,H3:H26)
D2:  =B2=C2

B3:  =PV(20%/12,36,-145/36,0)
C3:  =-NPV(20%/12,I3:I38)
D3:  =ABS(B3-C3) < 1E-11

B6:  =PV(NOMINAL(20%,12)/12,24,-130/24,0)
C6:  =-NPV(NOMINAL(20%,12)/12,H3:H26)
D6:  =B6=C6

B7: =PV(NOMINAL(20%,12)/12,36,-145/36,0)
C7: =-NPV(NOMINAL(20%,12)/12,I3:I38)
D7: =B7=C7

C10: =-XNPV(20%,H2:H26,G2:G26)
C11: =-XNPV(20%,I2:I38,G2:G38)

G2:G38: actual dates, the same day for each month

H3:  =-130/24
I3:  =-145/36
Copy H3 into H4:H26
Copy I3 into I4:I38


We could avoid the cash flow table in columns F:I by using some trickery in the formulas. But I want to keep the formulas simple in order to focus on the real issue here, namely the calculation of the NPV. And the cash flow table might make that clearer.

The calculations in column B demonstrate that for equal monthly cash flows, we could use the PV function instead of the NPV function. Column D demonstrates that the results are the same, at least to 11 decimal places (14 significant digits).

But the primary purpose of the example is to contrast, in column C, the NPV calculations based on simple and compounded rates, compared to the XNPV calculation based on the annual discount rate (20%).

If you used the simple monthly rate (20%/12) in C2 and C3 and a colleague used XNPV with the annual rate (20%) in C10 and C11, the results are very different. No surprise. But since the colleague used the corporate-mandated rate, you might have some difficult explaining to do.

However, if you used the compounded monthly rate (NOMINAL(20%,12)/12) in C6 and C7, the results are very similar to the XNPV results. The results are somewhat different. But the explanation is easy: NPV assumes equal periods ("monthly"), whereas XNPV uses the exact number of days between periods, which varies from 28 to 31.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Jyggalag
  • Question Question
Debt payment in Excel?
Replies
1
Views
216
Jyggalag
Jyggalag
D
  • Solved
Calculating the total interest paid on a car lease
Replies
4
Views
311
Domenic
D
V
  • Question Question
Volume Based Discounting - Total Cost Formula Question
Replies
4
Views
1K
Dave Patton
D
I
  • Question Question
Accrual Worksheet Next Pay date Formula
Replies
0
Views
266
ItalianPlatinum
I
K
  • Question Question
How to Find in Excel the total count of how many times each banks increase interest rate?
Replies
9
Views
823
James006
James006

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top