XPNV and amortization table not squarring off

[juanbolas]

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
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
4			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
5		Ene-20	Ene-21	Ene-22	Ene-23	Ene-24	Ene-25	Ene-26	Ene-27	Ene-28	Ene-29	Ene-30	Ene-31	Ene-32	Ene-33	Ene-34	Ene-35	Ene-36	Ene-37	Ene-38	Ene-39	Ene-40
6	16.264.820	Lease PMT	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
7	6,0%	Rate
8	196.153	XPNV
9	288.412	NPV	25.145	23.722	22.379	21.112	19.917	18.790	17.726	16.723	15.776	14.883	14.041	13.246	12.496	11.789	11.122	10.492	9.898	9.338	8.809	8.311
10
11
12	Initial Balance		288.412	280.572	272.261	263.451	254.113	244.215	233.723	222.601	210.812	198.316	185.070	171.029	156.146	140.369	123.646	105.920	87.130	67.213	46.101	23.722
13	Interest earned		17.305	16.834	16.336	15.807	15.247	14.653	14.023	13.356	12.649	11.899	11.104	10.262	9.369	8.422	7.419	6.355	5.228	4.033	2.766	1.423
14	Lease 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
15	Ending balance		280.572	272.261	263.451	254.113	244.215	233.723	222.601	210.812	198.316	185.070	171.029	156.146	140.369	123.646	105.920	87.130	67.213	46.101	23.722	- 0
16
17
18
19			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
20		Ene-20	Ene-21	Ene-22	Ene-23	Ene-24	Ene-25	Ene-26	Ene-27	Ene-28	Ene-29	Ene-30	Ene-31	Ene-32	Ene-33	Ene-34	Ene-35	Ene-36	Ene-37	Ene-38	Ene-39	Ene-40
21	16.264.820		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
22	6,0%
23	196.153
24	288.412
25
26
27	Initial Balance		196.153	- 429.219	859.047	- 1.794.782	3.672.106	- 7.589.683	15.609.601	- 32.180.924	66.267.558	###########	########	########	########	########	########	########	########	########	########	########
28	Interest earned		- 600.227	1.313.412	- 2.628.684	5.492.033	- 11.236.643	23.224.429	- 47.765.380	98.473.627	###########	###########	########	########	########	########	########	########	########	########	########	########
29	Lease 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
30	Ending balance		- 429.219	859.047	- 1.794.782	3.672.106	- 7.589.683	15.609.601	- 32.180.924	66.267.558	###########	###########	########	########	########	########	########	########	########	########	########	########
Sheet1

Cell Formulas
Range	Formula
G4:Z4,G19:Z19	G4	=F4+1
E6,E21	E6	='[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,G21	G6	='[Simulación vivienda interés social.xlsx]Esquema vivienda social'!$E$38
H6:Z6,H21:Z21	H6	=G6
E8,E23	E8	=XNPV(E7,G6:P6,G5:P5)
E9,E24	E9	=NPV($E$7,G6:Z6)
G9:Z9	G9	=G6/((1+$E$7)^(G4-1))
G12	G12	=E9
H12:Z12,H27:Z27	H12	=G15
G13:Z13	G13	=G12*((1+$E$7)-1)
G14:Z14,G29:Z29	G14	=-G6
G15:Z15,G30:Z30	G15	=SUM(G12:G14)
E22	E22	=E7
G27	G27	=E23
G28:Z28	G28	=FV($E$22,2,G27)-G27

 

[joeu2004]

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.

Book2
	A	B	C	D	E
1	1/1/2023	$100,000.00
2	1/1/2024	$101,000.00	$198,019.80	$200,000.00	$200,000.00
3	1/1/2025	$102,010.00	$297,029.70	$301,000.00	$299,997.27
Sheet1

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?

 

[joeu2004]

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]

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