Compound interest calculated daily

[Mas]

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

 

[Niek Otten]

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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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