I have recently started working for a new client and I am preparing commission statements for his consultants.
A couple of the consultants have been given loans by the company and they are now due to have interest applied to the loans. My client has asked me to "calculate the interest due assuming the draws and the repayments were made on the 21st of the month and the interest rate is 5.5%. " He also notes that interest should be capitalised and compounded monthly where payable.
Could someone please explain to me what he wants? I have not come across interest on loans before and I have no idea what capitalised and compounded means.
What i think he means is that any interest that is due isnt to be treated as paid but to be added to the loan balance.
eg in year one if the rate of interest is 5.5% and the loan is £1k then at the end of the year if none of the loan has been repaid the interest would be £55 and would be added to the loan balance due
Dr Loan interest (P&L) £55 Cr Loan (BS) £55
Compound interest simply means that any interest due is added to the loan and interest is calculated on the total amount eg at the end of year 2 if none of the loan or the interest has been paid the interest would be calculated as 5.5% x £1055 ie the combined total of the loan and year 1 interest.
Will depend how the interest is to be calculated as how it is to be worked out. It seems that the interest is to be calculated monthly/ (or maybe daily) so the interest would be compounded as the year goes on.
Would be best to check with you client to find out exactly how it is to be worked out.
I'm reading your clients request completely differently in that he's not after the accounting entries but rather the interest that is payable.
Applying a monthly interest rate seems wrong as not all months have the same number of days in them!
The implied daily interest rate at 5.5% is (as near as damn it) is a daily multiplier of 0.0001467
If you want to check that just set up a spreadsheet with 365 entries. start at £1000 and your last entry will be £1055.
So, put 1000 in A1 then 0.0001467 in B1 then in C1 enter the formula =a1+(a1*$b$1).
to allow for repayments we'll add another column D where any payments made can be added.
In column C2 enter the formula =(c1+(c1*$b$1))-d2 which will apply interest against the figure up to the previous day and then take off payments received.
Click on the bottom right hand handle on the cell and drag to line 365 and you should see a value of £1055.
Any payments receieved will automatically adjust the interest going forwards.
Might be a good idea to add a date column to the spreadsheet. Personally I would also seperate out the interest and the capital columns but I won't clutter up this reply with too many formula's.
Hope that this is what you are looking for,
kind regards,
Shaun.
-- Edited by Shamus on Tuesday 15th of February 2011 09:26:11 AM
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
