NPV Discount Rate

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.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
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?
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:

fcan12

New Member
Joined
Jan 25, 2019
Messages
2
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.

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?
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
As for the yearly discount rate, honestly there is a company wide discount rate
So, someone in the company finance dept should be able to tell you how they want you to determine sub-annual rates.

So it looks like using simple monthly discount rate calculation and picking the lowest NPV works in this case,right?
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.


ABCDEFGHI
1Simple RatePVNPV
Equal?pmt#24 pmts36 pmts
224 pmts$106.43$106.43TRUE0Jan'19$0.00$0.00
336 pmts$108.38$108.38
TRUE1Feb'19-$5.42-$4.03
4
2Mar'19-$5.42-$4.03
5Compounded RatePVNPVEqual?3Apr'19-$5.42-$4.03
624 pmts$108.11$108.11TRUE4May'19-$5.42-$4.03
736 pmts$110.84$110.84TRUE5Jun'19-$5.42-$4.03
8
6Jul'19-$5.42-$4.03
9Annual RateXNPV7Aug'19-$5.42-$4.03
1024 pmts$108.138Sep'19-$5.42-$4.03
1136 pmts$110.859Oct'19-$5.42-$4.03
1210Nov'19-$5.42-$4.03
1311Dec'19-$5.42-$4.03
[HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR]
2523Dec'20-$5.42-$4.03
2624Jan'21-$5.42-$4.03
2725Feb'21-$4.03
[HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR]
3735Dec'21-$4.03
3836Jan'22-$4.03

<tbody>
</tbody>
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.
 

Forum statistics

Threads
1,082,300
Messages
5,364,384
Members
400,796
Latest member
vrcdesktop

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top