NPV Discount Rate

[fcan12]

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]

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.

 

[fcan12]

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]

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.

	A	B	C	D	E	F	G	H	I
1	Simple Rate	PV	NPV	Equal?		pmt#		24 pmts	36 pmts
2	24 pmts	$106.43	$106.43	TRUE		0	Jan'19	$0.00	$0.00
3	36 pmts	$108.38	$108.38	TRUE		1	Feb'19	-$5.42	-$4.03
4						2	Mar'19	-$5.42	-$4.03
5	Compounded Rate	PV	NPV	Equal?		3	Apr'19	-$5.42	-$4.03
6	24 pmts	$108.11	$108.11	TRUE		4	May'19	-$5.42	-$4.03
7	36 pmts	$110.84	$110.84	TRUE		5	Jun'19	-$5.42	-$4.03
8						6	Jul'19	-$5.42	-$4.03
9	Annual Rate		XNPV			7	Aug'19	-$5.42	-$4.03
10	24 pmts		$108.13			8	Sep'19	-$5.42	-$4.03
11	36 pmts		$110.85			9	Oct'19	-$5.42	-$4.03
12						10	Nov'19	-$5.42	-$4.03
13						11	Dec'19	-$5.42	-$4.03
25						23	Dec'20	-$5.42	-$4.03
26						24	Jan'21	-$5.42	-$4.03
27						25	Feb'21		-$4.03
37						35	Dec'21		-$4.03
38						36	Jan'22		-$4.03

<tbody>
</tbody>

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.