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.
[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.