Compound interest and repayments on personal loan

[swiftiie]

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!!!

 

[Guest]

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.

 

[Guest]

Errata ....

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!

 

[Guest]

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

 

[Guest]

Errata (embellishment) ....

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?

 

[swiftiie]

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?!!!

 

[swiftiie]

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!