NPV Question

[hoops3335]

Hi All,

I have an excel formula i am trying to solve for. I have a table of loan payments that layout a monthly payment which looks like this

[TABLE="width: 650"]
<colgroup><col span="3"><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Start [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Principal[/TD]
[TD]Payment[/TD]
[TD]Interest[/TD]
[TD]Principal[/TD]
[TD]Principal[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD] 87,155,350[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 324,581[/TD]
[TD] 147,462[/TD]
[TD] 87,007,888[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2/1/2017[/TD]
[TD] 87,007,888[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 324,032[/TD]
[TD] 148,011[/TD]
[TD] 86,859,877[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD] 86,859,877[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 323,481[/TD]
[TD] 148,563[/TD]
[TD] 86,711,314[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD] 86,711,314[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 322,927[/TD]
[TD] 149,116[/TD]
[TD] 86,562,198[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]5/1/2017[/TD]
[TD] 86,562,198[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 322,372[/TD]
[TD] 149,671[/TD]
[TD] 86,412,527[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]6/1/2017[/TD]
[TD] 86,412,527[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 321,815[/TD]
[TD] 150,229[/TD]
[TD] 86,262,299[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]7/1/2017[/TD]
[TD] 86,262,299[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 321,255[/TD]
[TD] 150,788[/TD]
[TD] 86,111,511[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD] 86,111,511[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 320,694[/TD]
[TD] 151,350[/TD]
[TD] 85,960,161[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]9/1/2017[/TD]
[TD] 85,960,161[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 320,130[/TD]
[TD] 151,913[/TD]
[TD] 85,808,248[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]10/1/2017[/TD]
[TD] 85,808,248[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 319,564[/TD]
[TD] 152,479[/TD]
[TD] 85,655,769[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD] 85,655,769[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 318,996[/TD]
[TD] 153,047[/TD]
[TD] 85,502,722[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]12/1/2017[/TD]
[TD] 85,502,722[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 318,426[/TD]
[TD] 153,617[/TD]
[TD] 85,349,105[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD] 85,349,105[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 317,854[/TD]
[TD] 154,189[/TD]
[TD] 85,194,916[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD] 85,194,916[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 317,280[/TD]
[TD] 154,763[/TD]
[TD] 85,040,153[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD] 85,040,153[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 316,704[/TD]
[TD] 155,339[/TD]
[TD] 84,884,814[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD] 84,884,814[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 316,125[/TD]
[TD] 155,918[/TD]
[TD] 84,728,896[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]5/1/2018[/TD]
[TD] 84,728,896[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 315,545[/TD]
[TD] 156,499[/TD]
[TD] 84,572,397[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD] 84,572,397[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 314,962[/TD]
[TD] 157,081[/TD]
[TD] 84,415,315[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD] 84,415,315[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 314,377[/TD]
[TD] 157,666[/TD]
[TD] 84,257,649[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]8/1/2018[/TD]
[TD] 84,257,649[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 313,790[/TD]
[TD] 158,254[/TD]
[TD] 84,099,395[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]9/1/2018[/TD]
[TD] 84,099,395[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 313,200[/TD]
[TD] 158,843[/TD]
[TD] 83,940,552[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD] 83,940,552[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 312,609[/TD]
[TD] 159,435[/TD]
[TD] 83,781,118[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]11/1/2018[/TD]
[TD] 83,781,118[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 312,015[/TD]
[TD] 160,028[/TD]
[TD] 83,621,089[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]12/1/2018[/TD]
[TD] 83,621,089[/TD]
[TD="align: right"]$472,043[/TD]
[TD] 311,419[/TD]
[TD] 160,624[/TD]
[TD] 83,460,465[/TD]
[/TR]
</tbody>[/TABLE]

What I want to calculate is the NPV of the remaining payments after a specified date. The payment column has been bolded. For example, if the prepayment date is 3/1/2017 I want to calculate the NPV of all remaining payments for 3/1/2017 and after. Any advice on the appropriate formula?

Thanks

 

[Eric W]

My financial analysis classes are far past, so this may not be entirely accurate, but it seems reasonable. I replicated your table using formulas like so:

	A	B	C	D	E	F	G	H	I	J	K	L	M
Month	Day	Year	Date	Start Principal	Payment	Interest	Principal	End Principal	Interest	NPV until end
Annual interest

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"] $ 87,155,350.00 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]324,581[/TD]
[TD="align: right"]147,462[/TD]
[TD="align: right"]87,007,888[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.37%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 87,174,662.89 [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2/1/2017[/TD]
[TD="align: right"] $ 87,007,888.00 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]324,032[/TD]
[TD="align: right"]148,011[/TD]
[TD="align: right"]86,859,877[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 87,027,272.81 [/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"] $ 86,859,876.83 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]323,481[/TD]
[TD="align: right"]148,562[/TD]
[TD="align: right"]86,711,314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.47%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 86,879,333.83 [/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"] $ 86,711,314.44 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]322,927[/TD]
[TD="align: right"]149,116[/TD]
[TD="align: right"]86,562,199[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 86,730,843.90 [/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]5/1/2017[/TD]
[TD="align: right"] $ 86,562,198.77 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]322,372[/TD]
[TD="align: right"]149,671[/TD]
[TD="align: right"]86,412,528[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 86,581,800.97 [/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]6/1/2017[/TD]
[TD="align: right"] $ 86,412,527.78 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]321,815[/TD]
[TD="align: right"]150,228[/TD]
[TD="align: right"]86,262,299[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 86,432,202.98 [/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]7/1/2017[/TD]
[TD="align: right"] $ 86,262,299.39 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]321,255[/TD]
[TD="align: right"]150,788[/TD]
[TD="align: right"]86,111,512[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 86,282,047.86 [/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"] $ 86,111,511.52 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]320,694[/TD]
[TD="align: right"]151,349[/TD]
[TD="align: right"]85,960,162[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 86,131,333.54 [/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]9/1/2017[/TD]
[TD="align: right"] $ 85,960,162.09 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]320,130[/TD]
[TD="align: right"]151,913[/TD]
[TD="align: right"]85,808,249[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 85,980,057.93 [/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]10/1/2017[/TD]
[TD="align: right"] $ 85,808,249.01 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]319,564[/TD]
[TD="align: right"]152,479[/TD]
[TD="align: right"]85,655,770[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 85,828,218.95 [/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"] $ 85,655,770.18 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]318,996[/TD]
[TD="align: right"]153,047[/TD]
[TD="align: right"]85,502,723[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 85,675,814.49 [/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]12/1/2017[/TD]
[TD="align: right"] $ 85,502,723.50 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]318,426[/TD]
[TD="align: right"]153,617[/TD]
[TD="align: right"]85,349,107[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 85,522,842.46 [/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"] $ 85,349,106.84 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]317,854[/TD]
[TD="align: right"]154,189[/TD]
[TD="align: right"]85,194,918[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 85,369,300.73 [/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"] $ 85,194,918.09 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]317,280[/TD]
[TD="align: right"]154,763[/TD]
[TD="align: right"]85,040,155[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 85,215,187.18 [/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"] $ 85,040,155.12 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]316,704[/TD]
[TD="align: right"]155,339[/TD]
[TD="align: right"]84,884,816[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 85,060,499.69 [/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"] $ 84,884,815.78 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]316,125[/TD]
[TD="align: right"]155,918[/TD]
[TD="align: right"]84,728,898[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 84,905,236.12 [/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"] $ 84,728,897.94 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]315,544[/TD]
[TD="align: right"]156,499[/TD]
[TD="align: right"]84,572,399[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 84,749,394.33 [/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"] $ 84,572,399.43 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]314,962[/TD]
[TD="align: right"]157,081[/TD]
[TD="align: right"]84,415,318[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 84,592,972.15 [/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"] $ 84,415,318.09 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]314,377[/TD]
[TD="align: right"]157,666[/TD]
[TD="align: right"]84,257,652[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 84,435,967.43 [/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]8/1/2018[/TD]
[TD="align: right"] $ 84,257,651.76 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]313,789[/TD]
[TD="align: right"]158,254[/TD]
[TD="align: right"]84,099,398[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 84,278,377.99 [/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]9/1/2018[/TD]
[TD="align: right"] $ 84,099,398.25 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]313,200[/TD]
[TD="align: right"]158,843[/TD]
[TD="align: right"]83,940,555[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 84,120,201.67 [/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"] $ 83,940,555.37 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]312,609[/TD]
[TD="align: right"]159,434[/TD]
[TD="align: right"]83,781,121[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 83,961,436.28 [/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]11/1/2018[/TD]
[TD="align: right"] $ 83,781,120.94 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]312,015[/TD]
[TD="align: right"]160,028[/TD]
[TD="align: right"]83,621,093[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 83,802,079.61 [/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]12/1/2018[/TD]
[TD="align: right"] $ 83,621,092.76 [/TD]
[TD="align: right"]$472,043 [/TD]
[TD="align: right"]311,419[/TD]
[TD="align: right"]160,624[/TD]
[TD="align: right"]83,460,469[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 83,642,129.48 [/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"] $ 83,460,468.59 [/TD]
[TD="align: right"]$472,044 [/TD]
[TD="align: right"]310,821[/TD]
[TD="align: right"]161,223[/TD]
[TD="align: right"]83,299,245[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 83,481,583.66 [/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=E2*$K$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=F2-G2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=E2-H2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]M2[/TH]
[TD="align: left"]=SUMPRODUCT(F2:F$314/(1+$K$2)^(ROW(F2:F$314)-ROW(F1)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=I2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Using Goal-seek, I determined that the rate of interest on this table is .37% monthly, or 4.47% annually. This table continues on to row 314, where the final payment is made. Excel has a built-in NPV function, but it has a maximum of 251 values, and this table goes beyond that. However, using the formula in the Help section for NPV, I was able to create a formula that does the same thing.

Put the formula in M2, then drag down the column. You'll notice that the NPV is very close to the principal value, which is to be expected. It's probably not exactly the same due to variances in my formula (pay at start or end of month, etc.). Where it might change is if you forecast a change in the interest rate. You'd need to add a column in J with the expected monthly interest rate at that point in time, then adjust the formula in M to take that in consideration. (Change $K$2 to J2:J$314)

Hope this helps!

 

[Tetra201]

Here is my take on it:

Monthly interest rate (L2) =RATE(ROWS(F2:F25),F2,-E2,I25)
Annual interest rate (L3) =L2*12

Loan term (L5) =NPER(L2,F2,-E2)
Last payment date (L6) =EDATE(D2,L5)

Lookup date (L8) 3/1/2017
Present value (L9) =-PV(L2,DATEDIF(L8,L6,"m"),F2)