Calculate total Interest paid

Ricky62 · Jun 20, 2012

Hi Guys
My 1st post ever so please allow for any protocol errors.

I use Excel 2010 but any solution must cater for Excel 2003

Here's simple extract from spreadsheet - person owes £79.72 & is charged interest at 2.843% periodically on each statement. The extract shows how I currently calculate total interest to be paid on ONE account before debt is cleared.

Payment number	balance	Interest rate	interest charged	set payment rate	revised balance
1	£79.72	2.843%	£2.27	£11.84	£70.15
2	£70.15	2.843%	£1.99	£11.84	£60.30
3	£60.30	2.843%	£1.71	£11.84	£50.18
4	£50.18	2.843%	£1.43	£11.84	£39.76
5	£39.76	2.843%	£1.13	£11.84	£29.05
6	£29.05	2.843%	£0.83	£11.84	£18.04
7	£18.04	2.843%	£0.51	£11.84	£6.71
8	£6.71	2.843%	£0.19		£6.90
		Total Interest paid	£10.06

So total paid is £79.72 plus total interest payable of £10.06 - ie 1 payment @ £11.84, 6 @ £11.84, plus final 1 @ £6.90

I now need to do the calc for dozens of accounts every day; is there a formula to calc total interest to be paid

Thanks in advance

AhoyNC · Jun 20, 2012

This function may work for you.
CUMIPMT()

The Analysis ToolPak will need to be installed in 2003.
See link below.
 http://office.microsoft.com/en-us/excel-help/cumipmt-HP005209038.aspx

Ricky62 · Jun 22, 2012

Thanks for that

CUMIPMT gives a result close to the required answer but is always slightly more than it.

I wonder if Rounding is an issue

=-CUMIPMT(0.02843,8,79.72,1,8,0) gives 10.532 (against expected 10.06)

Any other suggestions;

by the way, to calc the total number of instalments needed to clear the Balance (plus reducing interest charges) I use a formula which starts (-log10(

MickG · Jun 22, 2012

The reason is because the formula expects each repayments to be the same amount.
Which should be £11.28, this then returns the interest as £10.532.
This is another way for the calculation.

Repayment	=PMT(0.0284,8,-79.72)	£11.28
Total Interest	=11.28*8-79.72	£10.52
Single Formula	=PMT(0.0284,8,-79.72)*8-79.72	£10.52

MickG · Jun 22, 2012

NB:- The last formula should be as below, but the forum Facilty did not paste correctly:-

Single Formula

=PMT(0.0284,8,-79.72)*8-79.72

£10.52

Ricky62 · Jun 23, 2012

The repayment amount must change (ie reduce) each period (because interest to be charged / paid is recalculated each period based on the reducing balance - ie opening balance plus interest minus repayment).

So 10.52 is close but not correct (it should be 10.06)

The other difficulty is that at the start of the calc you don't know how many periods it is going to take to clear the balance+interest. As mentioned above I've separately calculated the periods required (by using logs) & hard coded the answer (8) into the CUMIPMT / PMT formula.

As Excel can calc the number of periods to clear the reducing balance (8), I was hoping it could also calc the total interest charged as well.

MickG · Jun 23, 2012

If you don't know how many periods in the loan , how do you know what each repayment should be ????

Ricky62 · Jun 23, 2012

The repayment is fixed at an agreed amount & never varies (until the final instalment when reduced balance plus interest < repayment).

In this example the repayment is fixed at 11.84.

This agreed figure always exceeds interest applied during each period so that the balance continuously reduces until we arrive at the final payment (8) where the reduced balance is down to 6.71 plus interest at 0.19

xenou · Jun 23, 2012

I took a stab at this and failed to find anything super smart but this seems to work anyway (in principal it could be combined into one huge formula). Basic strategy is to compute the value of the loan at the end of the second last period, then using that you can quickly arrive at the last payment's interest to finish it off. A special case would be if the loan happens to be fully paid off with no remainder after some number of payments are made - in which case the calculated value for periods will be a whole number, or very close to a whole number, and remaining principal will be zero.

<img alt="screenshot" src="http://northernocean.net/etc/mrexcel/20120623_capture.jpg">

Ricky62 · Jun 23, 2012

Great reply

Thanks to all who responded

Calculate total Interest paid

Ricky62

New Member

Excel Facts

AhoyNC

Well-known Member

Ricky62

New Member

MickG

MrExcel MVP

MickG

MrExcel MVP

Ricky62

New Member

MickG

MrExcel MVP

Ricky62

New Member

xenou

MrExcel MVP

Ricky62

New Member

Similar threads

Forum statistics

Share this page

Calculate total Interest paid

New Member

Excel Facts

Well-known Member

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock