When using XNPV in Excel you need to make sure the discount rate you give it is the EFFECTIVE rate and not the nominal rate (which is the one normally quoted). To make it extra confusing the NPV function in Excel needs the NOMINAL Rate to get the right answer. So how to understand the XNPV and effective rate.
XNPV and effective rate as the discount rate
Below a quick test to compare which rate, nominal or effective, gets the closest to the correct NPV on a simple 12 month loan at a NOMINAL 11% interest rate.
Discount rate for NPV in Excel
As seen below, in rows 8 to 13 we have created an amortisation table and in C15 calculated the repayment that will amortise this loan to zero at an 11% interest rate.
If we work backwards, the cash flows in F18 to R18 should have a Net Present Value of 0 if we use the discount rate of 11% (matching the interest rate).
Using Excel’s NPV function this works when we use the nominal interest rate as the discount rate (B18) and the NPV result is 0.
If we rather used the effective rate of 11% compounded over 12 months (11.57%), the resultant NPV (cell C19) is out, so it seems that when using the NPV function you need to use the Nominal rate as the discount rate.
NPV’s opposite formula of IRR (cell D18) results in an IRR of 11% (remembering to multiply the result by 12). If you wanted the effective rate you would need to compound it yourself or else use the EFFECT function in Excel.
Discount rate for XNPV in Excel
When using XNPV however, the discount rate needs to be the EFFECTIVE rate. Same example below (note that we are going to be a little out as we have left the amortisation as is, i.e. interest calculated on 12 even months and not taking the number of days into account).
If we do a XNPV using the nominal rate as the discount rate (cell B20) the resultant net present value is out (cell C20). However, when we use the EFFECTIVE rate (cell B21) we are significantly closer (as per above it would never match due to the impact of number of days in a month).
The XIRR of this (cell D20) shows that Excel is immediately converting to the EFFECTIVE rate. So in this case you DO NOT need to use the EFFECT function to convert the result to an effective rate.
As a result it is important to decide which discount rate to use depending on which Excel function you use.
Perform a spreadsheet review on these XNPV calcs
If you are using the likes of NPV and XNPV you are building models in Excel. If you want to audit these models have a look at an Auditing tool to quickly find errors in Excel spreadsheets, including the unique error the NPV causes.