Calculating an Interest Rate in excel?

Didn't find your answer?

How do I use excel to calculate an interest rate when I know the following?

Intial value of lease
Monthly charge
Total charge over the period
Period in months

What I need is a formula to enter into the interest rate tab in the table below:

Contract Length Leased Asset Invoiced per month Total charge over lease period Interest rate
24 250 10 240 ??????

Any help would be much appreciated.

Replies (13)

Please login or register to join the discussion.

avatar
By Accountant A
10th Jan 2019 15:31

FTFY. Please could some kind person help me with a query.

Thanks (0)
RLI
By lionofludesch
10th Jan 2019 15:38

There are some interest rate templates on Excel.

The one I used last week needs the interest rate and then tells you the repayments but you just need to use trial and error to match up with the actual repayments. Bit tedious but it takes couple of minutes tops.

Thanks (0)
Replying to lionofludesch:
avatar
By Accountant A
10th Jan 2019 15:40

lionofludesch wrote:

The one I used last week needs the interest rate and then tells you the repayments but you just need to use trial and error to match up with the actual repayments. Bit tedious but it takes couple of minutes tops.

Can you not use "goalseek"?

https://support.office.com/en-us/article/use-goal-seek-to-find-the-resul...

Thanks (0)
Replying to Accountant A:
RLI
By lionofludesch
10th Jan 2019 15:56

Probably. Not sure it's much easier. I only had three goes to get it spot on.

Thanks (0)
Replying to Accountant A:
avatar
By AndySimpson
10th Jan 2019 16:15

Thank you for the idea but sadly goal seek will not work in this instance as I have a spreadsheet with a number of leases of varying values and terms so need a formula which can just be copied down.

The spreadsheet will also be added to each month.

Thanks (0)
avatar
By paulwakefield1
10th Jan 2019 16:01

Can you not just use the RATE function?

Thanks (0)
Replying to paulwakefield1:
RLI
By lionofludesch
10th Jan 2019 16:17

Didn't see that option in the template I used.

Thanks (0)
avatar
By paulwakefield1
10th Jan 2019 16:11

My previous response was a little curt. Apologies.

The Rate function will work if you have constant payments. It takes the form:

= RATE(number of periods 24, periodic payment -(250+240)/24 if I have understood the question, initial value 250). The last three arguments are optional and allow for a residual value, whether payments are made at the start or end of each period and an initial guess (default is 10%).

Thanks (3)
Replying to paulwakefield1:
RLI
By lionofludesch
10th Jan 2019 16:18

I'm too polite to have three arguments.

Thanks (0)
Replying to lionofludesch:
avatar
By paulwakefield1
10th Jan 2019 16:38

Just the 5 minute argument then?

Thanks (0)
Replying to paulwakefield1:
RLI
By lionofludesch
10th Jan 2019 16:43

No - I generally settle for disagreements.

Thanks (0)
Replying to lionofludesch:
avatar
By spidersong
10th Jan 2019 16:45

But this is AWEB, this is just for abuse.

If it's an argument you're after you want 12A next door.

EDIT: Crikey I need to be a bit quicker, already three in, I didn't expect that

Thanks (0)
Replying to spidersong:
avatar
By paulwakefield1
10th Jan 2019 16:55

No one expects............

Thanks (0)