Looking for Excel Spreadsheet Expert

RacingDriver

Yes, I don't think my formula is compounding correctly. Back to my macro solution then...

bbeat65

RacingDriver wrote: »

Ok, I think I understand what you are trying to do. To do this calculation you can use the VBA macro I have written below. If you are unsure how to add the VBA macro to Excel let me know what version of Excel you are using (e.g. 2010, 2016 etc.) and I can try and help.

This assumes that you have three values in your spreadsheet in the following cells:

Monthly Deposit in B1 (e.g. 100)
Annual Interest in B2 (e.g. 2)
Months in B3 (e.g. 12)

The macro then outputs your final value to cell B5

If your values are in different cells, you can update the macro below by changing the parts in red/bold in the format row, column

Sub InterestCalc()
'Tell Excel that the values we are going to use are numbers
Dim MonthlyDeposit As Variant
Dim MonthlyInterest As Variant
Dim Months As Variant
Dim Count As Variant
Dim FinalValue As Variant

'Read in values from spreadsheet;
MonthlyDeposit = Cells(1, 2) 'In format (row, column) (e.g. this is B1)
MonthlyInterest = ((Cells(2, 2) / 100) / 12) + 1 'B2
Months = Cells(3, 2) 'B3

For i = 1 To Months
FinalValue = FinalValue + (MonthlyDeposit * i) 'Add monthly deposit
FinalValue = FinalValue * MonthlyInterest 'Add monthly interest
Next i

Cells(5, 2) = FinalValue 'Output final value
End Sub

I am embarrassed to say - I havent got a clue how to do this. It is way over my head. I am using Excel 2016. If you have some free time, and want to help a novice - thats great. The way I did it, by using 1 row per investment, resulted in 240 rows (as you know), and about 7 pages, if I want to print it. It would be amazing to have it accomplish the same thing in 1 row, and be able to change the amount, rate, years/months, and still come out with the correct answer.

RacingDriver

bbeat65 wrote: »

I am embarrassed to say - I havent got a clue how to do this. It is way over my head. I am using Excel 2016. If you have some free time, and want to help a novice - thats great. The way I did it, by using 1 row per investment, resulted in 240 rows (as you know), and about 7 pages, if I want to print it. It would be amazing to have it accomplish the same thing in 1 row, and be able to change the amount, rate, years/months, and still come out with the correct answer.

1) Open Excel

2) Press Alt+F11 to open VBE

3) Click "ThisWorkbook" on the left hand side

4) Click Insert -> Module and paste my code above

bbeat65

I open Excel, and hit ALT+F11, and my screen gets dimmer...lol. I am feeling pretty lame. I guess I have a lot to learn.

RacingDriver

Example here https://files.fm/u/mfj277bn

Aegis

RacingDriver wrote: »

1) Open Excel

2) Press Alt+F11 to open VBE

3) Click "ThisWorkbook" on the left hand side

4) Click Insert -> Module and paste my code above

Good thing you're not putting anything malicious in there! I once sent someone some code they insisted I wrote for them, and I coded it to do what the wanted but about 1% of the time to scream "Nooooooooo" when the file was closed. That seemed like a fair price for the work I did!

bbeat65

Thank You Racing Driver. I dont know how you did it. Some day hope to. But - it works, and I am grateful.