Compound and simple interest VBA?

[Cathmac801]

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

 

[James006]

Hi Catherine,

Before getting into the automation process ...

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

[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

 

[Cathmac801]

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

 

[James006]

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