Caluclating interest on a loan - help (can/will pay)!!

Monsoon
Monsoon Registered Posts: 4,071 Beyond epic contributor 🧙‍♂️
Hi,

I have a client who has a business loan from a private individual, who has agreed an interest rate of, say 4% over base. They have told my client to calculate the interest themselves.

I know the formula is hideously complex and I have no practical knowledge of it. I am not confident in being able to explain how do do it or indeed of getting it right!

Also, surely my client could choose to pay based on either a daily rate, or balance outstanding at the end of every month/quarter/year? The loaner has said it's up to her to caluclate it. Loan is repayable quarterly.

Is it simply a case of, say:

Loan taken out January
2nd payment in March, balance outstanding £19000
Interest = 19000 * (4.5% x 1/4)
Plus the capital repayment of, say, £500
i.e. charging a quarter of a year's interest on that quarter's payment

Payment in June
Bal outstanding (19000-500)
Interest = 18500 * (4.5% x 1/4)
Plus capital repayment of 500

etc

??

Client happy to pay someone to work it out and provde a spreadsheet to work it out going forwards, including the possibility of changing interest rates etc.....

Thank you!

Comments

  • Monsoon
    Monsoon Registered Posts: 4,071 Beyond epic contributor 🧙‍♂️
    Ok, so having read this thread, I didn't get it right. I hope someone can help! My tiny brain can't cope! :lol:
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor 🧙‍♂️
    I'm sure someone with more loan experience will advise, but it is like the other thread you mention, in that you have to decide if the interest is compounded or not. In this case I wouldn't imagine it is supposed to.

    You need a spreadsheet with 3 columns; loan - interest - total

    Then you can work out the interest daily, monthly or quarterly, but keep it in a seperate column so its not compounded.
  • Monsoon
    Monsoon Registered Posts: 4,071 Beyond epic contributor 🧙‍♂️
    Thanks PGM. Maybe it isn't as complex as my little brain thinks.

    What is the practical difference between compounded and not? Obviously I see why savings interest that gets added to the account balance gets subject to compound interest (hooray!) but I'm not so clear on why loan interest would get compounded.

    Is the logic that in future years they will owe interest that they haven't paid yet and so they are paying interest on that as well, i.e. compounded?
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor 🧙‍♂️
    With the compound method as Crispy mentioned on the other thread you'd work out 4.5% to the 12th root.

    Which would be 1.003675% monthly on total balance.

    I think we need someone with more loan experience, any bankers?! :D
  • sdv
    sdv Registered Posts: 585 Epic contributor 🐘
    Monsoon wrote: »
    Hi,

    I have a client who has a business loan from a private individual, who has agreed an interest rate of, say 4% over base. They have told my client to calculate the interest themselves.

    I know the formula is hideously complex and I have no practical knowledge of it. I am not confident in being able to explain how do do it or indeed of getting it right!

    Also, surely my client could choose to pay based on either a daily rate, or balance outstanding at the end of every month/quarter/year? The loaner has said it's up to her to caluclate it. Loan is repayable quarterly.

    Is it simply a case of, say:

    Loan taken out January
    2nd payment in March, balance outstanding £19000
    Interest = 19000 * (4.5% x 1/4)
    Plus the capital repayment of, say, £500
    i.e. charging a quarter of a year's interest on that quarter's payment

    Payment in June
    Bal outstanding (19000-500)
    Interest = 18500 * (4.5% x 1/4)
    Plus capital repayment of 500

    etc

    ??

    Client happy to pay someone to work it out and provde a spreadsheet to work it out going forwards, including the possibility of changing interest rates etc.....

    Thank you!

    Why don't you use PMT formula in the spreadsheet.

    Given the loan amount, £19000

    interest rate, (0.5+4) =4.5%

    Length of Loan = 20 ( 5years times 4 qts)

    gives a qtly payment of £1066.19

    Formula = PMT((4.5/4),20,£19000)

    Note 4.5% is divided by 4 for a quarter (12 for monthly payments)
    20 = length of loan


    The interesting thing of the above formula is you can change any variable to suit your needs

    If you want the spread sheet just PM me your e-mail

    hope this helps
  • slackda
    slackda Registered Posts: 460 Dedicated contributor 🦉
    or use the loan amortization spread sheet template in excel?

    great for showing a complete breakdown of the capital element and interest element of a loan
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor 🧙‍♂️
    slackda wrote: »
    or use the loan amortization spread sheet template in excel?

    great for showing a complete breakdown of the capital element and interest element of a loan

    Thats a handy spreadsheet! The blood pressure tracker is a bit of an odd one...

    Going back to Monsoons original question, with it being a add hoc type loan? How do you treat the interest?
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor 🧙‍♂️
    slackda wrote: »
    or use the loan amortization spread sheet template in excel?

    great for showing a complete breakdown of the capital element and interest element of a loan

    I've been playing round with spreadsheets and loan amounts. This template/compound interest does give the more meaningfull figures!
  • Monsoon
    Monsoon Registered Posts: 4,071 Beyond epic contributor 🧙‍♂️
    Thanks guys.
    My brain just doesn't get this stuff, especially how to calculate the initial payment (but my client has used various loan calculators online and they all come out with the same answer, so I guess that's right). I really, really don't want to advise her on this stuff as it's way outside what I'm comfortable doing. I like to think I'm pretty intelligent and good at what I do, but I feel like an utter thicko with this! :D

    Where are the templates in excel? I didn't know they had any...

    PGM, PM incoming...!
  • slackda
    slackda Registered Posts: 460 Dedicated contributor 🦉
    depends which version of excel your using?
    but i think on 2007 its when you go to new file you select templates, if its not there you can download from the excel online templates

    link for you http://office.microsoft.com/en-us/templates/loan-amortization-schedule-TC001019777.aspx?CTT=5&origin=HA001034640
  • Monsoon
    Monsoon Registered Posts: 4,071 Beyond epic contributor 🧙‍♂️
    slackda wrote: »
    depends which version of excel your using?
    but i think on 2007 its when you go to new file you select templates, if its not there you can download from the excel online templates

    link for you http://office.microsoft.com/en-us/templates/loan-amortization-schedule-TC001019777.aspx?CTT=5&origin=HA001034640

    Yes I'm on 2007.

    Look at that! Isn't that clever!! OOOhhhh!!! :D

    And the loan amortization one works perfectly, thank you both!!
    with it being a add hoc type loan? How do you treat the interest?
    Not sure what you mean. It's a formal loan, but just from a private individual not a bank. I would just debit the interest to loan interest paid just as you would a bank loan.
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor 🧙‍♂️
    Monsoon wrote: »
    Not sure what you mean. It's a formal loan, but just from a private individual not a bank. I would just debit the interest to loan interest paid just as you would a bank loan.

    Ignore what I said, I was getting muddled up with the compound calculation.
  • JodieR
    JodieR Registered Posts: 1,002 Beyond epic contributor 🧙‍♂️
    I remember working out loan interest in maths classes at school - could do it back then but wouldn't like to try it now! The compounding bit is how often the interest is added isn't it? so if you had a loan at 5% compounded weekly you'd pay more than if it was compounded monthly as you'd be paying more interest on the interest that's already been added. But that's about all I can remember!
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor 🧙‍♂️
    JodieR wrote: »
    I remember working out loan interest in maths classes at school - could do it back then but wouldn't like to try it now! The compounding bit is how often the interest is added isn't it? so if you had a loan at 5% compounded weekly you'd pay more than if it was compounded monthly as you'd be paying more interest on the interest that's already been added. But that's about all I can remember!

    That's what was throwing me, but no! The compounding should take this into account and get you to the same answer. Actually the more periods and he sooner you might take into any repayments sooner, and reduce interest payments.
Privacy Policy