XPNV and amortization table not squarring off

juanbolas

New Member
Hello everyone,

I'm working on a financial model with leases. I have to calculate the lease payments in the future, discount them and use the discounted value plus interest and the lease payment in an amortization table.

All is well as you can see on the first part of the sheet where I use NPV. But when I try to use XNPV all hell breaks loose. I tried an interest formula similar to the way XNPV handles rates ((1+r)^((t1-t0)/365)-1) but no luck,

Can anyone help me with the exact formular I need to use? Thanks in advance!

Juan

Help!.xlsx
EFGHIJKLMNOPQRSTUVWXYZ
41234567891011121314151617181920
5Ene-20Ene-21Ene-22Ene-23Ene-24Ene-25Ene-26Ene-27Ene-28Ene-29Ene-30Ene-31Ene-32Ene-33Ene-34Ene-35Ene-36Ene-37Ene-38Ene-39Ene-40
616.264.820 Lease PMT 25.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.145
76,0% Rate
8196.153 XPNV
9288.412 NPV 25.14523.72222.37921.11219.91718.79017.72616.72315.77614.88314.04113.24612.49611.78911.12210.4929.8989.3388.8098.311
10
11
12Initial Balance288.412280.572272.261263.451254.113244.215233.723222.601210.812198.316185.070171.029156.146140.369123.646105.92087.13067.21346.10123.722
13Interest earned17.30516.83416.33615.80715.24714.65314.02313.35612.64911.89911.10410.2629.3698.4227.4196.3555.2284.0332.7661.423
14Lease payment- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145
15Ending balance280.572272.261263.451254.113244.215233.723222.601210.812198.316185.070171.029156.146140.369123.646105.92087.13067.21346.10123.722- 0
16
17
18
191234567891011121314151617181920
20Ene-20Ene-21Ene-22Ene-23Ene-24Ene-25Ene-26Ene-27Ene-28Ene-29Ene-30Ene-31Ene-32Ene-33Ene-34Ene-35Ene-36Ene-37Ene-38Ene-39Ene-40
2116.264.82025.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.14525.145
226,0%
23196.153
24288.412
25
26
27Initial Balance196.153- 429.219859.047- 1.794.7823.672.106- 7.589.68315.609.601- 32.180.92466.267.558###########################################################################################
28Interest earned- 600.2271.313.412- 2.628.6845.492.033- 11.236.64323.224.429- 47.765.38098.473.627######################################################################################################
29Lease payment- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145- 25.145
30Ending balance- 429.219859.047- 1.794.7823.672.106- 7.589.68315.609.601- 32.180.92466.267.558######################################################################################################
Sheet1
Cell Formulas
RangeFormula
G4:Z4,G19:Z19G4=F4+1
E6,E21E6='[Simulación vivienda interés social.xlsx]Esquema vivienda social'!\$C\$30*'[Simulación vivienda interés social.xlsx]Esquema vivienda social'!\$E\$32*'[Simulación vivienda interés social.xlsx]Esquema vivienda social'!\$E\$33
G6,G21G6='[Simulación vivienda interés social.xlsx]Esquema vivienda social'!\$E\$38
H6:Z6,H21:Z21H6=G6
E8,E23E8=XNPV(E7,G6:P6,G5:P5)
E9,E24E9=NPV(\$E\$7,G6:Z6)
G9:Z9G9=G6/((1+\$E\$7)^(G4-1))
G12G12=E9
H12:Z12,H27:Z27H12=G15
G13:Z13G13=G12*((1+\$E\$7)-1)
G14:Z14,G29:Z29G14=-G6
G15:Z15,G30:Z30G15=SUM(G12:G14)
E22E22=E7
G27G27=E23
G28:Z28G28=FV(\$E\$22,2,G27)-G27

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

joeu2004

Banned user
Unfortunately, your XL2BB snippet includes references to an external file, which obviously we do not have. Consequently, I cannot copy the XL2BB snippet into a working Excel file.

Excel NPV and XNPV will differ because Excel NPV assumes cash flows that are truly regularly spaced (e.g. "yearly"), wheresas XNPV calculates the actual differences between dates -- which is 366, not 365, for leap years.

Also, Excel NPV discounts the first amount in the range, effectively discounting to the end of the period before (or equivalently, to the beginning of first period), whereas XNPV discounts to the first date.

These differences are demonstrated below.

Rich (BB code):
``````Formulas:
B2: =B1*(1+1%)
C2: =NPV(1%, \$B\$1:B2)
D2: =NPV(1%,\$B\$2:B2) + B1
E2: =XNPV(1%, \$B\$1:B2, \$A\$1:A2)
B3: =B2*(1+1%)
C3: =NPV(1%, \$B\$1:B3)
D3: =NPV(1%, \$B\$2:B3) + B2
E3: =XNPV(1%, \$B\$1:B3, \$A\$1:A3)``````

Note that C2 and D2 differ because in D2, B1 is not discounted, which is what we usually intend.

Note that D2 and E2 are the same because they include only 365-day periods.

However, D3 and E3 differ because they include a 366-day period.

Also, the "date precision" of XNPV is questionable because for dates spanning leap years, XNPV still divides by 365. So even though the difference between 1/1/2024 and 1/1/2025 is "one year", XNPV calculate 366/365, which is 1.0027397260274 years.

PS.... You also highlight G13 and G28. Do you have a question about their values?

Last edited:

joeu2004

Banned user
ERRATA....
D2: =NPV(1%,\$B\$2:B2) + B1
[....]
D3: =NPV(1%, \$B\$2:B3) + B2

I just noticed an error: the value in D3 is \$301,000 instead of \$300,000.

My mistake: in D3, "+ B2" should be "+ \$B\$1". And in D2, my intent was for B1 to be \$B\$1 so that the formula could be copied down.

juanbolas

New Member
Thanks for the reply and sorry for the delay in getting back to you. I was sick.

I got another example working but it doesn't involve the long series. I'll upload it later.

Again, thanks!
Juan

Replies
1
Views
312
Replies
2
Views
306
Replies
0
Views
162
Replies
9
Views
298
Replies
2
Views
277

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,571
Messages
5,765,175
Members
425,266
Latest member
CPAgirl

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.

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

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