Compound interest calculated daily

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mas - 02 Nov 2006 21:11 GMT

Hi all,

I would like to create a worksheet that will calculate the daily
interest of a compound interest account between two specified dates that
I enter.

For example

A1 is the balance of my account
A2 is the interest (%)
A3 is the start date (i.e. 1 Nov 2006)
A4 is the end date (i.e. 30 Nov 2006)

I would like:

Column A starting at row 10 to populate with the date starting at the
start date and ending at the end date

Column B starting at row 10 to be the balance of my account, and then
populate with an updated balance after the daily compound interested is
calculated in Column C (i.e. B11=B10+C10)

Column C starting at row 10 to calculate the compound interest daily
(i.e C11 = the compound interest on the updated balance of B11)

All 3 columns should automatically update starting from the start and
ending at the end date.

Hope this is clearly understood.

Thanks in advance.

Mas

Reply to this Message

Niek Otten - 02 Nov 2006 21:44 GMT

In A10:
=A3
In B10:
=A1
In C10:
=B10*$A$2

In A11:
=IF(A10="","",IF(A10+1>$A$4,"",A10+1))
In B11:
=IF(A11="","",B10+C10)
In C11:
=IF(A11="","",B11*$A$2)

Now copy A11, B11 and C11 down as far as you need for the maximum number of days.

Important is how you specify the interest rate. The way it works now you need to enter a daily rate. If you want to enter a yearly
rate, you need to specify how the daily rate is calculated: divide by 360, by 365, by the real number of days, or use the EFFECT
function to calculate the effective rate from the nominal rate.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi all,
|
[quoted text clipped - 29 lines]
|
| Mas

Reply to this Message