XNPV function

[askall1000]

Hello All, If somebody help with my my question I will be very thankful. Thank you very much in advance. My question is that when I use XNPV function in payment schedule as attached which I know only the monthly irregular payments and I want to discount all the payments to 1 January with taking consideration of the dates to prepare amortization schedule, but I could not get zero balance at the end of my table in balance column (with NPV function I can get zero by the way), I think it is related with interest calculation. I would be greatful if somebody help me to solve this problem.

Numbers are big in the table but originally they are not.

Book1
	A	B	C	D	E	F
2	Rent Period (in Year):		2
3	Rent Period (Month):		30
4	Interest (Yearly):		0,27
5	Interest (Monthly):		0,0225
6	XNPV		872,980,659,565,032.00
7
8	#	Date	Total Payment	Principal Payment	Interest	Balance
9						872,980,659,565,032.00
10	0	01.01.2019	27132,7	27132,7		845,847,959,565,032.00
11	1	01.02.2019	33189,84	141,582,609,097,868.00	190,315,790,902,132.00	831,689,698,655,245.00
12	2	01.03.2019	33189,84	14,476,821,780,257.00	18,713,018,219,743.00	817,212,876,874,988.00
13	3	01.04.2019	39246,98	208,596,902,703,128.00	183,872,897,296,872.00	796,353,186,604,675.00
14	4	01.05.2019	33189,85	152,719,033,013,948.00	179,179,466,986,052.00	78,108,128,330,328.00
15	5	01.06.2019	33189,85	156,155,211,256,762.00	175,743,288,743,238.00	765,465,762,177,604.00
16	6	01.07.2019	33189,85	159,668,703,510,039.00	172,229,796,489,961.00	7,494,988,918,266.00
17	7	01.08.2019	33189,85	163,261,249,339,015.00	168,637,250,660,985.00	733,172,766,892,698.00
18	8	01.09.2019	53620,3	371,239,127,449,143.00	164,963,872,550,857.00	696,048,854,147,784.00
19	9	01.10.2019	40,000.00	243,389,007,816,749.00	156,610,992,183,251.00	671,709,953,366,109.00
20	10	01.11.2019	40,000.00	248,865,260,492,625.00	151,134,739,507,375.00	646,823,427,316,847.00
21	11	01.12.2019	40,000.00	254,464,728,853,709.00	145,535,271,146,291.00	621,376,954,431,476.00
22	12	01.01.2020	40,000.00	260,190,185,252,918.00	139,809,814,747,082.00	595,357,935,906,184.00
23	13	01.02.2020	40,000.00	266,044,464,421,109.00	133,955,535,578,891.00	568,753,489,464,073.00
24	14	01.03.2020	40,000.00	272,030,464,870,584.00	127,969,535,129,416.00	541,550,442,977,015.00
25	15	01.04.2020	40,000.00	278,151,150,330,172.00	121,848,849,669,828.00	513,735,327,943,998.00
26	16	01.05.2020	40,000.00	284,409,551,212,601.00	115,590,448,787,399.00	485,294,372,822,738.00
27	17	01.06.2020	40,000.00	290,808,766,114,884.00	109,191,233,885,116.00	456,213,496,211,249.00
28	18	01.07.2020	40,000.00	297,351,963,352,469.00	102,648,036,647,531.00	426,478,299,876,002.00
29	19	01.08.2020	40,000.00	3,040,423,825,279.00	959,576,174,721,005.00	396,074,061,623,212.00
30	20	01.09.2020	40,000.00	310,883,336,134,777.00	891,166,638,652,228.00	364,985,728,009,735.00
31	21	01.10.2020	40,000.00	31,787,821,119,781.00	821,217,888,021,903.00	333,197,906,889,954.00
32	22	01.11.2020	40,000.00	32,503,047,094,976.00	749,695,290,502,396.00	300,694,859,794,978.00
33	23	01.12.2020	40,000.00	33,234,365,654,613.00	6,765,634,345,387.00	267,460,494,140,365.00
34	24	01.01.2021	40,000.00	339,821,388,818,418.00	60,178,611,181,582.00	233,478,355,258,523.00
35	25	01.02.2021	40,000.00	347,467,370,066,832.00	525,326,299,331,676.00	19,873,161,825,184.00
36	26	01.03.2021	40,000.00	355,285,385,893,336.00	447,146,141,066,639.00	163,203,079,662,506.00
37	27	01.04.2021	40,000.00	363,279,307,075,936.00	367,206,929,240,638.00	126,875,148,954,912.00
38	28	01.05.2021	40,000.00	371,453,091,485,145.00	285,469,085,148,553.00	897,298,398,063,978.00
39	29	01.06.2021	40,000.00	379,810,786,043,561.00	201,892,139,564,395.00	517,487,612,020,418.00
40				82,123,189,836,299.00	33,790,716,163,701.00
Sheet1

Cell Formulas
Range	Formula
C5	C5	=+ROUND(C4/12,4)
C6	C6	=+XNPV(C4,C10:C39,B10:B39)
D10, D11:D39	D10	=+C10-E10
F9	F9	=+C6
F10, F11:F39	F10	=+F9-D10
A11:A39	A11	=+IF(A10>=$C$3,"",A10+1)
B11:B39	B11	=+EDATE(B10,1)
E11:E39	E11	=+F10*$C$5
D40:E40	D40	=SUM(D10:D39)

 

[StephenCrump]

Book1
	A	B	C	D	E	F
1
2
3
4	Interest (Yearly):		27%
5
6	XNPV		872,980.66
7
8		Date	Total Payment	Principal Payment	Interest	Balance
9						872,980.66
10	0	1/01/2019	27,132.70	27,132.70		845,847.96
11	1	1/02/2019	33,189.84	15,843.60	17,346.24	830,004.36
12	2	1/03/2019	33,189.84	17,830.88	15,358.96	812,173.48
13	3	1/04/2019	39,246.98	22,591.32	16,655.66	789,582.15
14	4	1/05/2019	33,189.85	17,524.96	15,664.89	772,057.19
15	5	1/06/2019	33,189.85	17,356.88	15,832.97	754,700.31
16	6	1/07/2019	33,189.85	18,217.00	14,972.85	736,483.31
17	7	1/08/2019	33,189.85	18,086.41	15,103.44	718,396.90
18	8	1/09/2019	53,620.30	38,887.77	14,732.53	679,509.14
19	9	1/10/2019	40,000.00	26,518.90	13,481.10	652,990.23
20	10	1/11/2019	40,000.00	26,608.79	13,391.21	626,381.44
21	11	1/12/2019	40,000.00	27,572.93	12,427.07	598,808.51
22	12	1/01/2020	40,000.00	27,719.93	12,280.07	571,088.58
23	13	1/02/2020	40,000.00	28,288.39	11,711.61	542,800.19
24	14	1/03/2020	40,000.00	29,593.52	10,406.48	513,206.67
25	15	1/04/2020	40,000.00	29,475.41	10,524.59	483,731.26
26	16	1/05/2020	40,000.00	30,403.03	9,596.97	453,328.23
27	17	1/06/2020	40,000.00	30,703.37	9,296.63	422,624.87
28	18	1/07/2020	40,000.00	31,615.35	8,384.65	391,009.52
29	19	1/08/2020	40,000.00	31,981.37	8,018.63	359,028.15
30	20	1/09/2020	40,000.00	32,637.23	7,362.77	326,390.92
31	21	1/10/2020	40,000.00	33,524.58	6,475.42	292,866.34
32	22	1/11/2020	40,000.00	33,994.04	6,005.96	258,872.30
33	23	1/12/2020	40,000.00	34,864.11	5,135.89	224,008.19
34	24	1/01/2021	40,000.00	35,406.15	4,593.85	188,602.04
35	25	1/02/2021	40,000.00	36,132.24	3,867.76	152,469.80
36	26	1/03/2021	40,000.00	37,178.60	2,821.40	115,291.20
37	27	1/04/2021	40,000.00	37,635.66	2,364.34	77,655.54
38	28	1/05/2021	40,000.00	38,459.36	1,540.64	39,196.18
39	29	1/06/2021	40,000.00	39,196.18	803.82	0.00
Sheet1

Cell Formulas
Range	Formula
C6	C6	=XNPV(C4,C10:C39,B10:B39)
D10, D11:D39	D10	=C10-E10
F9	F9	=C6
F10, F11:F39	F10	=F9+E10-C10
E11:E39	E11	=F10*((1+C$4)^((B11-B10)/365)-1)

 

[askall1000]

Thank you very much.