# Loan Calc With A Difference!?

#### crag1978

##### New Member
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Andrew Poulsom

##### MrExcel MVP
Welcome to MrExcel.

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

Hi Craig

#### Richard Schollar

##### MrExcel MVP
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

##### New Member

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

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

#### crag1978

##### New Member
Hi

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

#### Richard Schollar

##### MrExcel MVP
Do you mean like this?

#### crag1978

##### New Member
Richard

That is exactly it!

Many thanks for your swift response

Craig

Replies
8
Views
667
Replies
0
Views
752
Replies
8
Views
1K
Replies
4
Views
272
Replies
3
Views
671

1,191,175
Messages
5,985,110
Members
439,940
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back