Interest Computation using multiple Interest Rates

Topic

New Reply

I have a worksheet where i need to calculate the following

1) No Of days at say 9%, 9.5% etc
2) Calculate of Interest on each amount
3) Calculate Accumulated Interest
4) Calculate Accumulated Int & Advances

Your assistance will be most appreciated

Regards

Howard

Reply | Quote

Replies

I have a worksheet where i need to calculate the following

1) No Of days at say 9%, 9.5% etc
2) Calculate of Interest on each amount
3) Calculate Accumulated Interest
4) Calculate Accumulated Int & Advances

Your assistance will be most appreciated

Regards

Howard

What is your interest interval, annual, monthly, daily?

Reply | Quote

What is your interest interval, annual, monthly, daily?

Thanks for the reply. Need to calculate interest daily

Your assistance in this regard will be most appreciated

Regards

Howard

Reply | Quote

What is your interest interval, annual, monthly, daily?

Hi Jezza

Thanks for the reply

My Interest interval is is daily

Regards

Howard

Reply | Quote

I have a worksheet where i need to calculate the following

1) No Of days at say 9%, 9.5% etc
2) Calculate of Interest on each amount
3) Calculate Accumulated Interest
4) Calculate Accumulated Int & Advances

Your assistance will be most appreciated

Regards

Howard

How often is the interest compounded? Daily?
Are all answers to be calculated to the time of calculation (today)?

Reply | Quote

I have a worksheet where i need to calculate the following

1) No Of days at say 9%, 9.5% etc
2) Calculate of Interest on each amount
3) Calculate Accumulated Interest
4) Calculate Accumulated Int & Advances

Your assistance will be most appreciated

Regards

Howard

The attached solution is based on the financial status as of the date of calculation (now) with interest compounded daily.

It requires that:

[*]the “Interest Rate Table” be a named range (IRT);
[*]the dates in the Interest Rate Table be actual dates, as received they were not recognizable as dates;
[*]a third column be added to the Interest Rate Table identifying the duration of each rate regime.

Should you wish to work with a different end date than now, the formula in F:F will need to refer to the appropriate cell(s).

Reply | Quote

The attached solution is based on the financial status as of the date of calculation (now) with interest compounded daily.

It requires that:

[*]the “Interest Rate Table” be a named range (IRT);
[*]the dates in the Interest Rate Table be actual dates, as received they were not recognizable as dates;
[*]a third column be added to the Interest Rate Table identifying the duration of each rate regime.

Should you wish to work with a different end date than now, the formula in F:F will need to refer to the appropriate cell(s).

Hi Don

Thanks for the help. I need you to calculate the interest on each of the advances from Date of the advance to now at the varying rates of Interest

Your assistance will be most appreciated

Regards

Howard

Reply | Quote

Thanks for the help. I need you to calculate the interest on each of the advances from Date of the advance to now at the varying rates of Interest

I need your help to understand. I see four transactions which are unrelated but subject to the the same variable rate interest regime.

Discussing the first loan:

[*]Date borrowed: 18 Aug 2005
[*]Principal loaned: 65,000.00
[*]Initial annual interest: 9.00%
[*]Principal plus interest at 8 Jun 2006 when the interest changed to 9.50% 69,886.43
[*]Principal plus interest at 14 Dec 2006 when the interest changed to 10.00% 73,409.18
[*]Principal plus interest today (13 Sep 2009) 96,649.18
[*]Accumulated interest over the total period 31,649.18

Please correct my understanding.

Reply | Quote

I need your help to understand. I see four transactions which are unrelated but subject to the the same variable rate interest regime.

Discussing the first loan:

[*]Date borrowed: 18 Aug 2005
[*]Principal loaned: 65,000.00
[*]Initial annual interest: 9.00%
[*]Principal plus interest at 8 Jun 2006 when the interest changed to 9.50% 69,886.43
[*]Principal plus interest at 14 Dec 2006 when the interest changed to 10.00% 73,409.18
[*]Principal plus interest today (13 Sep 2009) 96,649.18
[*]Accumulated interest over the total period 31,649.18

Please correct my understanding.

Hi Don

Thanks for the reply. The Principal amount loaned on 18 Aug 2005 was 65000.00 at 9%. The Interest on the 65000 will be @ 9% 65000 from 18/08/2005 until 07/06/2006 and then @ 9.5% up to 13/12/2006 and therafter at 10% up to now

Hope this is a lot clearer. If not, please let me know

Thanks for your patience & help so far

Regards

Howard

Reply | Quote

Hi Don

Thanks for the reply. The Principal amount loaned on 18 Aug 2005 was 65000.00 at 9%. The Interest on the 65000 will be @ 9% 65000 from 18/08/2005 until 07/06/2006 and then @ 9.5% up to 13/12/2006 and therafter at 10% up to now

Hope this is a lot clearer. If not, please let me know

Thanks for your patience & help so far

Regards

Howard

Howard

The attached provides in the various columns:

[*]D – Total interest to date on a given advance
[*]E – Cumulative total interest on advances
[*]F – Total of Advance plus interest to date on a given advance
[*]G – Cumulative value of column F

Please be specific about what you want changed.

Reply | Quote

Howard

The attached provides in the various columns:

[*]D – Total interest to date on a given advance
[*]E – Cumulative total interest on advances
[*]F – Total of Advance plus interest to date on a given advance
[*]G – Cumulative value of column F

Please be specific about what you want changed.

Hi Don

Thanks for the help so far. The Interest on say the first amount of 65000.00 needs to be calculated as follows:

65000 @9% up until 07/06/2006 , which is the period at which the Interest rate remained at 9% from 08/06/2006 until 13/12/2006 @9.5% and thereafter until now at the latest Interest Rate. I want this principle to be applied to the Interest calculation on all the amounts as there are varying interest rates to be charged. The computation is to be the same as the banks would calculate the interest on an overdraft or deposit. Apply the same principle as the bank would apply when calculating the interest on a deposit, savings or overdraft account where the interest rate changes and there are multiple capital amounts on which Interest is calculated daily

Hope this is a lot clearer of what i’m trying to do

Regards

Howard

Reply | Quote

Hi Don

Thanks for the help so far. The Interest on say the first amount of 65000.00 needs to be calculated as follows:

65000 @9% up until 07/06/2006 , which is the period at which the Interest rate remained at 9% from 08/06/2006 until 13/12/2006 @9.5% and thereafter until now at the latest Interest Rate. I want this principle to be applied to the Interest calculation on all the amounts as there are varying interest rates to be charged. The computation is to be the same as the banks would calculate the interest on an overdraft or deposit. Apply the same principle as the bank would apply when calculating the interest on a deposit, savings or overdraft account where the interest rate changes and there are multiple capital amounts on which Interest is calculated daily

Hope this is a lot clearer of what i’m trying to do

Regards

Howard

Is that not what I have provided?

Reply | Quote

Is that not what I have provided?

Hi Don

My apologies, did not realise that you set up a UDF function to take the varying Interest rates into account

Thanks for all the help, much appreciated

Regards

Howard

Reply | Quote

Is that not what I have provided?

Hi Don

I changed the date of cell A11 to 14/12/2006, so that I can check the Interest calculation. My calculation comes to $1239.04, Your Calc $1426.19.

Please check your UDF formula and advise

Thanks

Howard

Reply | Quote

Hi Don

I changed the date of cell A11 to 14/12/2006, so that I can check the Interest calculation. My calculation comes to $1239.04, Your Calc $1426.19.

Please check your UDF formula and advise

Thanks

Howard

Hi Howard

The difference is that I am compounding daily; you are not compounding at all. The calculation is provided at cell C20.

Reply | Quote

Hi Howard

The difference is that I am compounding daily; you are not compounding at all. The calculation is provided at cell C20.

Hi Don

Thanks for the help. I see where I went wrong

Regards

Howard

Reply | Quote