Pmt

[Wimpie]

Hi all

Please assist. I need to determine the Payment on a loan of 20 000 with 14% interest over 10 years. I can not use the =pmt functions and need to show the loan reduction and interest amount per payment for each year. the loanis payable yearly and must be fully paid by year 10

 

[markmzz]

Wimpie,

If I understand correctly what you want, so I think this can help you:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #C5D9F1;;">Loan</td><td style="text-align: right;;">$20,000.00 </td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">Year</td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> Start </td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> Interest </td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> Amount </td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> End </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #C5D9F1;;">Interest</td><td style="text-align: right;;">14%</td><td style="text-align: right;;"></td><td style="text-align: center;;">2011</td><td style="text-align: right;;">$20,000.00</td><td style="text-align: right;;">$2,800.00</td><td style="text-align: right;;">$1,034.27</td><td style="text-align: right;;">$18,965.73</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #C5D9F1;;">Years</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: center;;">2012</td><td style="text-align: right;;">$18,965.73</td><td style="text-align: right;;">$2,655.20</td><td style="text-align: right;;">$1,179.07</td><td style="text-align: right;;">$17,786.66</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #C5D9F1;;">Payment</td><td style="text-align: right;;">($3,834.27)</td><td style="text-align: right;;"></td><td style="text-align: center;;">2013</td><td style="text-align: right;;">$17,786.66</td><td style="text-align: right;;">$2,490.13</td><td style="text-align: right;;">$1,344.14</td><td style="text-align: right;;">$16,442.52</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2014</td><td style="text-align: right;;">$16,442.52</td><td style="text-align: right;;">$2,301.95</td><td style="text-align: right;;">$1,532.32</td><td style="text-align: right;;">$14,910.20</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2015</td><td style="text-align: right;;">$14,910.20</td><td style="text-align: right;;">$2,087.43</td><td style="text-align: right;;">$1,746.84</td><td style="text-align: right;;">$13,163.36</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2016</td><td style="text-align: right;;">$13,163.36</td><td style="text-align: right;;">$1,842.87</td><td style="text-align: right;;">$1,991.40</td><td style="text-align: right;;">$11,171.96</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2017</td><td style="text-align: right;;">$11,171.96</td><td style="text-align: right;;">$1,564.07</td><td style="text-align: right;;">$2,270.20</td><td style="text-align: right;;">$8,901.77</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2018</td><td style="text-align: right;;">$8,901.77</td><td style="text-align: right;;">$1,246.25</td><td style="text-align: right;;">$2,588.02</td><td style="text-align: right;;">$6,313.74</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2019</td><td style="text-align: right;;">$6,313.74</td><td style="text-align: right;;">$883.92</td><td style="text-align: right;;">$2,950.35</td><td style="text-align: right;;">$3,363.40</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2020</td><td style="text-align: right;;">$3,363.40</td><td style="text-align: right;;">$470.88</td><td style="text-align: right;;">$3,363.40</td><td style="text-align: right;;">$0.00</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=B1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=E2*B$2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=-B$4-F2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=E2-G2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=H2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=PMT(<font color="Blue">B2,B3,B1</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[shg]

Is this a different question than the thread at http://www.mrexcel.com/forum/showthread.php?t=568063?

 

[markmzz]

Maybe this (whitout PMT function):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #C5D9F1;;">Loan</td><td style="text-align: right;;">$20,000.00 </td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">Year</td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> Start </td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> Interest </td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> Amount </td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> End </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #C5D9F1;;">Interest</td><td style="text-align: right;;">14%</td><td style="text-align: right;;"></td><td style="text-align: center;;">2011</td><td style="text-align: right;;">$20,000.00</td><td style="text-align: right;;">$2,800.00</td><td style="text-align: right;;">$1,034.27</td><td style="text-align: right;;">$18,965.73</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #C5D9F1;;">Years</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: center;;">2012</td><td style="text-align: right;;">$18,965.73</td><td style="text-align: right;;">$2,655.20</td><td style="text-align: right;;">$1,179.07</td><td style="text-align: right;;">$17,786.66</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #C5D9F1;;">Payment</td><td style="text-align: right;;">$3,834.27 </td><td style="text-align: right;;"></td><td style="text-align: center;;">2013</td><td style="text-align: right;;">$17,786.66</td><td style="text-align: right;;">$2,490.13</td><td style="text-align: right;;">$1,344.14</td><td style="text-align: right;;">$16,442.52</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2014</td><td style="text-align: right;;">$16,442.52</td><td style="text-align: right;;">$2,301.95</td><td style="text-align: right;;">$1,532.32</td><td style="text-align: right;;">$14,910.20</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2015</td><td style="text-align: right;;">$14,910.20</td><td style="text-align: right;;">$2,087.43</td><td style="text-align: right;;">$1,746.84</td><td style="text-align: right;;">$13,163.36</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2016</td><td style="text-align: right;;">$13,163.36</td><td style="text-align: right;;">$1,842.87</td><td style="text-align: right;;">$1,991.40</td><td style="text-align: right;;">$11,171.96</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2017</td><td style="text-align: right;;">$11,171.96</td><td style="text-align: right;;">$1,564.07</td><td style="text-align: right;;">$2,270.20</td><td style="text-align: right;;">$8,901.77</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2018</td><td style="text-align: right;;">$8,901.77</td><td style="text-align: right;;">$1,246.25</td><td style="text-align: right;;">$2,588.02</td><td style="text-align: right;;">$6,313.74</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2019</td><td style="text-align: right;;">$6,313.74</td><td style="text-align: right;;">$883.92</td><td style="text-align: right;;">$2,950.35</td><td style="text-align: right;;">$3,363.40</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2020</td><td style="text-align: right;;">$3,363.40</td><td style="text-align: right;;">$470.88</td><td style="text-align: right;;">$3,363.40</td><td style="text-align: right;;">$0.00</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=B1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=E2*B$2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=B$4-F2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=E2-G2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=H2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=B1/(<font color="Blue">(<font color="Red">(<font color="Green">1+B2</font>)^B3-1</font>)/(<font color="Red">(<font color="Green">1+B2</font>)^B3*B2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Wimpie]

Hi Mark

The one posted at 9:21 is what I need. It is how ever the first time I encounter ^ in excel can you explain this to me so that I can understand how the formula work. I see that you use it everytime the term is used. what does it do to the term of 10 years?

Thanx

 

[brettdj]

It raises to the power


So if you had $1 compounding at 5% for 10 years then after 10 years you would have
= (1+5%)^10
= 1.62

Cheers

Dave

 

[markmzz]

Hi Mark

The one posted at 9:21 is what I need. It is how ever the first time I encounter ^ in excel can you explain this to me so that I can understand how the formula work. I see that you use it everytime the term is used. what does it do to the term of 10 years?

Thanx

Wimpie,

I will try to complete the explanation of Dave with a simpler example of loan/payments.

I created names for each argument of formula to facilitate the identification of each one of them in the formula. In this example, the value is $ 100 borrowed, the interest rate is 10% per year and the period is 10 years.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;background-color: #C5D9F1;;">Loan</td><td style="text-align: right;;">$100.00 </td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">Year</td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> Start </td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> Interest </td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> Amount </td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"> End </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;background-color: #C5D9F1;;">Interest</td><td style="text-align: right;;">10%</td><td style="text-align: right;;"></td><td style="text-align: center;;">2011</td><td style="text-align: right;;">$100.00</td><td style="text-align: right;;">$10.00</td><td style="text-align: right;;">$6.27</td><td style="text-align: right;;">$93.73</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;background-color: #C5D9F1;;">Period</td><td style="text-align: right;;">10</td><td style="text-align: center;;">Formula</td><td style="text-align: center;;">2012</td><td style="text-align: right;;">$93.73</td><td style="text-align: right;;">$9.37</td><td style="text-align: right;;">$6.90</td><td style="text-align: right;;">$86.82</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;background-color: #C5D9F1;;">Payment</td><td style="text-align: right;;">$16.27 </td><td style=";">=Loan/(((1+Interest)^Period-1)/((1+Interest)^Period*Interest))</td><td style="text-align: center;;">2013</td><td style="text-align: right;;">$86.82</td><td style="text-align: right;;">$8.68</td><td style="text-align: right;;">$7.59</td><td style="text-align: right;;">$79.23</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2014</td><td style="text-align: right;;">$79.23</td><td style="text-align: right;;">$7.92</td><td style="text-align: right;;">$8.35</td><td style="text-align: right;;">$70.88</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;background-color: #C5D9F1;;">Total of interest</td><td style="text-align: right;;">$62.75</td><td style="text-align: right;;"></td><td style="text-align: center;;">2015</td><td style="text-align: right;;">$70.88</td><td style="text-align: right;;">$7.09</td><td style="text-align: right;;">$9.19</td><td style="text-align: right;;">$61.69</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;background-color: #C5D9F1;;">Total of amount</td><td style="text-align: right;;">$100.00</td><td style="text-align: right;;"></td><td style="text-align: center;;">2016</td><td style="text-align: right;;">$61.69</td><td style="text-align: right;;">$6.17</td><td style="text-align: right;;">$10.11</td><td style="text-align: right;;">$51.59</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;background-color: #C5D9F1;;">Total of payment</td><td style="text-align: right;;">$162.75</td><td style=";">=Payment*Period</td><td style="text-align: center;;">2017</td><td style="text-align: right;;">$51.59</td><td style="text-align: right;;">$5.16</td><td style="text-align: right;;">$11.12</td><td style="text-align: right;;">$40.47</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2018</td><td style="text-align: right;;">$40.47</td><td style="text-align: right;;">$4.05</td><td style="text-align: right;;">$12.23</td><td style="text-align: right;;">$28.25</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2019</td><td style="text-align: right;;">$28.25</td><td style="text-align: right;;">$2.82</td><td style="text-align: right;;">$13.45</td><td style="text-align: right;;">$14.80</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2020</td><td style="text-align: right;;">$14.80</td><td style="text-align: right;;">$1.48</td><td style="text-align: right;;">$14.80</td><td style="text-align: right;;">$0.00</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">Total</td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="text-align: right;background-color: #C5D9F1;;">$62.75</td><td style="text-align: right;background-color: #C5D9F1;;">$100.00</td><td style="text-align: right;background-color: #C5D9F1;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td><td style="text-align: center;;">**********</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet41</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=Loan</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=Start*Interest</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=Payment-F2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=Start-Amount</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=H2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=Loan/(<font color="Blue">(<font color="Red">(<font color="Green">1+Interest</font>)^Period-1</font>)/(<font color="Red">(<font color="Green">1+Interest</font>)^Period*Interest</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=F12</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=G12</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=Payment*Period</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F12</th><td style="text-align:left">=SUM(<font color="Blue">F2:F11</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G12</th><td style="text-align:left">=SUM(<font color="Blue">Amount</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Amount</th><td style="text-align:left">=Sheet41!$G$2:$G$11</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Interest</th><td style="text-align:left">=Sheet41!$B$2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Loan</th><td style="text-align:left">=Sheet41!$B$1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Payment</th><td style="text-align:left">=Sheet41!$B$4</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Period</th><td style="text-align:left">=Sheet41!$B$3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Start</th><td style="text-align:left">=Sheet41!$E$2:$E$11</td></tr></tbody></table></td></tr></table><br />
I hope this have helped.

Markmzz