Compound interest and repayments on personal loan

To anyone out there!!!

This is a little complicated (and maybe a bit personal), but I need help in working out formulas for an excel worksheet.

I have a personal loan of $12,000 (at an interest rate of 10.4% per annum) which my partner is paying out. However, I have nominated my bank to deduct $800 per fortnight to finance this loan and my partner pays me whenever he can.

I would like to keep track of everything - the balance, interest, my repayments, and what my partner owes me.

I know how to set up the first 4 columns for the date/balance/interest/my repayments, but it doesn't coincide with my bank statments. I think this is because interest is calculated daily but added monthly. Is there a formula to relate to this, rather than the interest added to the principle on a daily basis?

Also, I'd like to work out how much my partner owes me. For example, if I pay $800 fortnightly and he gives me $500 in the first week and another $500 in the second week, then that means he owes me a total of $600. Is there a formula to add this automatically rather than me working it out all the time?

Please help!!!

My experience is only average with excel, so please reply in laymen terms

Thanks!!!

"swiftiie" wrote:
> I have a personal loan of $12,000 (at an interest rate of
> 10.4% per annum) [...].
> I know how to set up the first 4 columns for the
> date/balance/interest/my repayments, but it doesn't coincide
> with my bank statments. I think this is because interest is
> calculated daily but added monthly. Is there a formula to
> relate to this, rather than the interest added to the principle
> on a daily basis?

One of the following formula's should come close. Which one
depends partly on whether "10.4% per annum" is the APR,
which already takes daily compounding into account, or the
nominal annual rate. It also depends on whether your lender
uses 360 or 365 to determine the daily rate. Both are common.

If 10.4% is the APR, try the following with 360 or 365:

=RATE(365,, -1, 1 + 10.4%)

If 10.4% is the nominal rate, try the following with 360 or 365:

=FV(10.4%/365, 365,, -1) - 1

That is just the daily rate. To compute the interest compounded
during a month, try:

=(PreviousBalance)
*((1 + DailyRate)^(ThisPeriodDate - PreviousPeriodDate) - 1)

Alternatively and perhaps easier to remember and understand:

=(PreviousBalance)
*(FV(DailyRate, ThisPeriodDate - PreviousPeriodDate,, -1) - 1)

Your lender may or may not compute the interest with
ROUND(...,2). It will be difficult to tell unless you have many
periods of statements from the lender to compare with.

Errata ....

I wrote:
> If 10.4% is the nominal rate, try the following with 360 or 365:
> =FV(10.4%/365, 365,, -1) - 1

Ah, simply 10.4%/365. The FV() above would be the APR.
Klunk!

"swiftiie" wrote:
> Also, I'd like to work out how much my partner owes me.
> For example, if I pay $800 fortnightly and he gives me $500
> in the first week and another $500 in the second week, then
> that means he owes me a total of $600. Is there a formula
> to add this automatically rather than me working it out all
> the time?

Assuming that your partner's payments, if any, are on about
the same date as your fortnightly payments, try the following.

A2 = payment to lender; ostensibly $800[*]
B2 = cumulative payment to lender: =B1+A2
C2 = partner's payment (e.g, $500)
D2 = cumulative partner's payment: =D1+C2
E2 = partner's debt: =B2-D2
[*] A2 might be the formula:

=IF(ROUND(PreviousBalance + ThisPeriodInterest, 2) > 800,
800, ROUND(PreviousBalance + ThisPeriodInterest, 2))

Alternatively, you might simply use 800 for most payments
and a different formula for the last formula, namely:

=PreviousBalance + ThisPeriodInterest

Errata (embellishment) ....

I wrote:
> To compute the interest compounded during a month, try:

I really meant "during a period", where the "period" for these
computations are the dates of your payments, not the monthly
statement.

I assume that when you say you pay "fortnightly", you really
mean twice a month, perhaps the 1st and 15th of the month.

Otherwise, if your statements are monthly and your payments
are truly every 2 weeks, which is out of sync with the statements,
you will have to make some adjustments in the structure of
your worksheet and in the formulas I offered. Is this complication
truly necessary?

Thanks for your help! I will give it a go anyway. I'm a bit confused with all the jargon, but I think I'll manage.

Is this complication really necessary?........
NO! (hehehe!) Honestly, I had the impression that excel is pretty smart with calculations - especially financial ones!!! I thought there was an easy way to do this, which I wasn't aware of! Obviously not!!!

Basically, I just wanted to set this up and enter any extra payments I made so that I didn't have to manually calculate final figures every fortnight.

It's also so that I can keep track if the bank has over-charged me (which has happened before) and how much my partner owes me. Hey - fair is fair, right?!!!

What does FV and APR mean?

Does IF and ROUND mean anything as well, or are they just part of the formula?

Thanks again!!! Sorry - I'm not a bright spark like you!