# Compound and simple interest VBA?

#### Cathmac801

##### New Member
Hi all, hoping someone can help me solve a complex query I have.
I have a large spreadsheet which has unique reference numbers in col A and month-year (ie oct-06) across row 1 (from oct-06 to mar-18).
The fiscal year for the spreadsheet is from 1-April to 31-March which is causing me problems!
For each individual ref no payments are filled in under some of the months (always consecutive and usually for between 6-12 months), the rest of the fields for that ref no are blank.
I need to work out the interest that should have been applied to those payments.
The rule is - use compound interest for each payment made based on the relevant fiscal year (I have an interest table that has the % broken down per month- it is the same for each 12 month period from apr to mar) - once the compound interest has been applied to the monthly amount then use simple interest (from same table) for each year afterwards up until end March 2018.
I have tried it in excel using formula but am now thinking VBA May have been a better option. Any help greatly appreciated.
Catherine

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi Catherine,

Before getting into the automation process ...

you would need to make sure you can properly apply the basic formula :

Code:
``[B]A*(1+i)^n[/B]``

or dig into Excel's financial functions :

https://www.excelfunctions.net/excel-financial-functions.html

Hope this will help

Thanks James for the reply. So I have “A” being the payment amount on the main sheet, then I have “I” on The factor table sheet (needs to lookup which factor is relevant to the fiscal year the payment was made in) and “n” would be a subtraction from the month-year the payment was made and the next 31-April (I found this excel formala v tricky). Do you think I should try and work this into a separate sheet in excel first? I have done the calc on a single basis but the spreadsheet is sizeable and formula are very broken down so was hoping VBA could be the answer.

Many thanks
Catherine

Hello again,

Indeed a draft spreadsheet would be a perfect initial step to ensure everything is fine ...

You are right "A" is the Amount ... whereas "I" is the Interest rate and "N" the number of periods ...

Make sure payment frequency is in line with the interest rate ... e.g. with a interest rate per year, the Number of periods is expressed in years ...

Hope this will help

Replies
0
Views
167
Replies
4
Views
131
Replies
6
Views
457
Replies
3
Views
370
Replies
1
Views
1K

1,196,412
Messages
6,015,117
Members
441,871
Latest member
lajervik

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back