Need Help in Calculating Multiple Loan Transactions

hi everyone,
If anyone have a interest calculation excel sheet laid out please message or send me link please

Comments

  • +4

    Use a spreadsheet and interest formulas

  • Agree, with spreadsheet work out the daily rate of interest and apply liberally.

  • +2

    2.75% simple interest is 0.0075% per day isn't it?

    Can you work it out off the daily balance?

  • +5

    I have a spreadsheet template that will do the calculations.
    Simply enter the date and amount (outs as a negative)for each transaction.
    The spreadsheet will calculate the interest since the last transaction date and the running balance.
    PM an email address if you would like a copy.

    • Don't spoonfeed him!

    • Thanks Jeff I have sent you an email

  • +6

    500k? Would your brother like another brother?

    • +1

      The max net amount he had in the account at any one time looks like it was just over $300,000, but still, wish I had a brother to offset my loans like that!

  • +1

    I've calculated you owing him $7,868.57 with simple interest (based on 2.75%/365.25 = 0.007529% per day). I'd be interested to see someone else's calculation.

    • Man, I think you are right. My wife had the similar calculation but she was not sure if was right or wrong. Thanks a lot I will do the calculations again and hope it will match and the problem will be solved.

    • hi mate,
      just realised transaction going out on 3 Jan 2017 is actually 3 Jan 2018. it was a mistake and I have corrected it now.

  • Agree with others - use daily rate, apply to balance outstanding:

    Column A: Date Column B: Credits Column C: Debits

    Column D: Balance [sum(column A to this line) - sum(column B to this line)]

    Column E: Days from last entry [Column A this line - Column A line above]

    Column F: Balance from previous line x Days from last entry.

  • On the 9th row of Date Out, you have 3-Jan-2017. Is it meant to be 2018?

    Banks calculate and accumulate interest daily and credit it monthly, typically on the last business day of the month.

    I have a spreadsheet that calculates this, but with interest added on the last day of the month, rather than last business day.

    Assuming the above mentioned transaction was in 2018, the current balance of the account as of today, 13-Aug-2019, is $8,949.32 with $8.77 accumulated interest.

    • just want to make sure that I cleared out all his money on 5th April 2019.

      • You have given back all the money he has given you on that day, but if you were to calculate accumulative interest and credit it similar to the banks do, then the account still had $8,856.07 balance and $14.82 accumulated interest on that day and has been accumulating and crediting further since.

        Of course, it all depends on how you and/or your brother want or have agreed to calculate interest.

        • Yeah thanks mate. It was supposed to simple interest and stops as soon as money was paid back. He had the luxury of taking out any money anytime he wanted so he don't get the extras what banks get

          • @DisabledUser249845:

            so he don't get the extras what banks get

            Or compound interest

          • +1

            @DisabledUser249845: My calculation of simple interest in your case is $8,561.45

            • +2

              @Trung: Hey mate, I really appreciate the time and effort. Thanks a lot for your help

  • it was 3 jan 2018. you guessed it right and thanks forr pointing that out

  • How come you're laundering your brothers money?

    O.o

    • Is it possible that he worked hard for his money and we help each other out cos we are brothers? Do you think it is little insulting to just throw such remarks without any info?

      • Ease up.

        Let me ask another way: Why didn't your brother have his own bank account when earning $500k in 3 years?

        Most people seeing 500k shifted around like this would naturally jump to the following conclusions: Tax dodge or money laundering.

        • Uh, what are you on.

          Family members lend family members money all the time.

          • @zeggie: What does one lend a family member 500k for over 3 years for? Especially in 100k amounts. Wtf. And it all gets paid back basically straight away.

            The transaction log looked like a tax dodge to me.

            • @Skramit: Happens all the time.

              Family member 1 needs money. Family member 2 has money to give. Better off paying interest to a family member (usually) than giving that interest to a Bank making billions a year and dealing with the application process.

              • @zeggie: But the transaction log doesn't fit your scenario IMO. Random amounts and frequency varying from a few thousand to 100 thousand over 3 years totaling 500k? (OP has now removed the log).

                Smelled dodgy to me shrug I should probably work for the ATO.

                • @Skramit: You do understand the fundamentals of compound interest, right?

  • For all those playing at home, please get something drafted before agreeing to this yourselves with a family member or friend.

    Things can happen, loss of job, relationship sours, and suddenly they have an equity claim on your home.

    • +1

      Or end up on Judge Judy.

Login or Join to leave a comment