Formula for interest payable

[parky123]

Hi There
Can anyone give me a formula for calculating the interest payable on a loan, based on:
Amount of loan;
Interest rate;
Repayment amount;
Repayment frequency.

e.g £3000 loan at 12% paid at £120/month.

I can get it right one payment at a time, but would like a single formula to do the lot.
All the formulae I can find use "number of payments", rather than a specific amount and frequency of payments.

I know there are lots of loan calculators out there, but I want to do lots on a spreadsheet.

Thanks in anticipation

 

[MickG]

Hi, How longs the loan suppose to be for, because that loan is paid off somewhere between 28 & 29 months.???

 

[parky123]

This loan is for as long as it takes to pay off at £120 per month. This particluar one takes 28 months plus a final payment of £109.47.
It's the way we work, allowing people to send a round figure for loan repayments rather than equal payments over a specific time.

This is what is making it hard to find a formula as it is not based on number of periods.

 

[Richard Schollar]

I think you basically want NPER:

Excel Workbook
	A	B
1	Amount of loan;	3000
2	Interest rate;	12%
3	Repayment amount;	120
4	Repayment frequency.	12
5	No of payments	28.91181
6	Cumulative interest paid	469.4172
Sheet1

Excel 2002

Cell Formulas
Range	Formula
B5	=NPER(B2/B4,-B3,B1)
B6	=B5*B3-B1

 

[parky123]

Thanks - that's perfect!

 

[MickG]

Hi, Athough you have your ans, if you ever have a power cut, you can use this equation to return the "Nper".

<TABLE style="WIDTH: 323pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=429 border=0 x:str><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362" width=256><TBODY><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 17.25pt; BACKGROUND-COLOR: #ccffcc" width=86 height=23>Rate</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=58>Pv</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 22pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=29>Pmt</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 192pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=256> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ccffff" align=right width=86 height=17 x:num="0.12">12.00%</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" align=right width=58 x:num>3000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 22pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" align=right width=29 x:num>120</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD></TR><TR style="HEIGHT: 7.5pt; mso-height-source: userset" height=10><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 7.5pt; BACKGROUND-COLOR: #ccffcc" width=86 height=10> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc"> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 22pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=29> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 192pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=256> </TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ccffff" align=right width=86 height=22 x:num="28.911809737480798" x:fmla="=NPER(A2/12,C2,-B2)">28.91180974</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:str="Function ">Function </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">:-</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 192pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=256 x:str="'=NPER(A2/12,C2,-B2)">=NPER(A2/12,C2,-B2)</TD></TR><TR style="HEIGHT: 7.5pt; mso-height-source: userset" height=10><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 7.5pt; BACKGROUND-COLOR: #ccffcc" width=86 height=10> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=58> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc"> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 192pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=256> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ccffff" align=right height=17 x:num="28.911809737480802" x:fmla="=LOG(+C2/(C2-(B2*A2/12)))/LOG(1+A2/12)">28.91180974</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Equation</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">:-</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:str="'=LOG(C2/(C2-(B2*A2/12)))/LOG(1+A2/12)">=LOG(C2/(C2-(B2*A2/12)))/LOG(1+A2/12)</TD></TR><TR style="HEIGHT: 6.75pt; mso-height-source: userset" height=9><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 6.75pt; BACKGROUND-COLOR: #ccffcc" height=9> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=58></TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 22pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=29></TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 192pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=256> </TD></TR></TBODY></TABLE>

Regards Mick