Loan Calc With A Difference!?

[crag1978]

Hi Guys

After a few days reading the forums and trawling the net I have a drawn a blank and request the benefit of your wisdom

I am looking for a formula that will allow me to find the amount left after a fixed amount of payments at x% interest. i.e I know the monthly payment, the interest rate and the loan duration.

I also know the capital cost, the amount remaining is the figure I require (It will be the down payment the customer would have to make)

Any ideas?

Thanks

Crag

 

[Andrew Poulsom]

Welcome to MrExcel.

Have a look at the CUMPRINC function in the Analysis ToolPak Add-In.

 

[Richard Schollar]

Hi Craig

How about this:

<b>Excel 2002</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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Initial Value</td><td style="text-align: right;;">£200,000.00</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;">2</td><td style=";">Term (months)</td><td style="text-align: right;;">60</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;">3</td><td style=";">Rate</td><td style="text-align: right;;">5%</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;">4</td><td style=";">Number of payments made to date</td><td style="text-align: right;;">15</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;">5</td><td style=";">Payment amount</td><td style="text-align: right;;">-£3,774.25</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;">6</td><td style=";">Capital outstanding</td><td style="text-align: right;;">£154,576.13</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;">7</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;">8</td><td style=";">Proof</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;">9</td><td style=";">t</td><td style=";">Principal</td><td style=";">Interest</td><td style=";">Payment</td><td style=";">Balance</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;;">-200000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-200000</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">-833.333</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-197059</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">-821.08</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-194106</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">-808.775</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-191140</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">-796.419</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-188163</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">-784.011</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-185172</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">-771.552</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-182170</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">-759.04</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-179154</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">-746.477</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-176127</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">-733.861</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-173086</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">-721.193</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-170033</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">-708.472</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-166967</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;">-695.698</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-163889</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;">-682.871</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-160798</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;">-669.99</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-157693</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;">-657.055</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-154576</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;">-644.067</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-151446</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;">-631.025</td><td style="text-align: right;;">£3,774.25</td><td style="text-align: right;;">-148303</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</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></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">Sheet1</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">B5</th><td style="text-align:left">=PMT(<font color="Blue">B3/12,B2,B1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=B1+CUMPRINC(<font color="Blue">B3/12,B2,B1,1,B4,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E10</th><td style="text-align:left">=SUM(<font color="Blue">B10:D10,E9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C11</th><td style="text-align:left">=E10*$B$3/12</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D11</th><td style="text-align:left">=-$B$5</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E11</th><td style="text-align:left">=SUM(<font color="Blue">B11:D11,E10</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Richard Schollar]

You can also use FV to generate this eg in the above the formula:

=FV(B3/12,B4,B5,B1)

Will give the same result as B6 (well, -£154,576).

 

[crag1978]

Thanks for the reply Andrew.

I have just read my post and I have obviously misled.....

Although I know the Capital Cost, this is not the pv, so CUMPRINC will not work for me

In essence I need to know the down payment required in order to complete the loan. The remainder of the capital cost will be made up of the loan e.g £300 per month at 5% for 48 months.

As you can tell describing problems is not my forte!

Thanks again......

 

[Richard Schollar]

Can you provide an example (keep it simple) so that we can see which number it is you are after?

 

[crag1978]

Hi

I have (hopefully) attached an example!

Excel Workbook
	A	B	C	D	E
1		Annual Earnings - Vfe = Vf + Ve		4,292.56	This is the annual amount of revenue the system I provide will generate
2
3		Mean Monthly Earnings (Me)		357.71	This is the amount of revenue the system I provide will generate per month
4
5		System Cost
6		Fully installed cost (inc VAT)		39,857.99	This is the capital cost to install the system
7
8
9		Finance Details
10		Down Payment Required		-	This is the down payment cost - I do not know what this is!
11		Finance Amount		-	This is the total finance required. It will be the captial cost less the balance of (357.71 @ 5% * 48)
12		Interest Rate		5%
13		Loan Term (Months)		48
14		Cost of Finance		??	I will use CUMIPMT for this once I have the other numbers!
Pg 5 Dom (2)

Excel 2010

 

[Richard Schollar]

Do you mean like this?
<b>Excel 2002</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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Monthly Amount</td><td style="text-align: right;;">-£357.71</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Term</td><td style="text-align: right;;">48</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Rate</td><td style="text-align: right;;">5%</td><td style="text-align: right;;"></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></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Present Value</td><td style="text-align: right;;">£15,532.83</td><td style="text-align: right;;"></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></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Capital Cost</td><td style="text-align: right;;">£39,857.99</td><td style="text-align: right;;"></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></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Downpayment</td><td style="text-align: right;;">£24,325.16</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</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;">15</td><td style=";">Cost of finance</td><td style="text-align: right;;">-£1,637.25</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</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;">17</td><td style=";">PV+Cost of Finance</td><td style="text-align: right;;">£17,170.08</td><td style="text-align: right;;">£17,170.08</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">Sheet1</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">K9</th><td style="text-align:left">=PV(<font color="Blue">K7/12,K6,K5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K13</th><td style="text-align:left">=K11-K9</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K15</th><td style="text-align:left">=CUMIPMT(<font color="Blue">K7/12,K6,K9,1,K6,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K17</th><td style="text-align:left">=K9-K15</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L17</th><td style="text-align:left">=-K5*K6</td></tr></tbody></table></td></tr></table><br />

 

[crag1978]

Richard

That is exactly it!

Many thanks for your swift response

Craig