0

I would like to make an excel formula according to this situation:

Person A / Person B Person A invests 1000$ every year from 18 to 28, and then holds it until age 65. It compounds every year with 8%.

Person B invests 0$ every year from 18 to 28 but then starts to invest 1000$ every year until age 65. It compounds every with with 8%.

I want 47 rows (65-18) for Person A & B and finally a "total". I want to see it grow in every cell.

Anyone that could help me set it up and perhaps link it to a an upload service? I want to learn... :) Thanks! :)

  • Does person B's investment compound annually too? It says "every with with" currently. – Hart CO Sep 7 at 14:39
  • 4
    Can you tell us what part you are having trouble with? – Craig W Sep 7 at 16:14
5

One of the most powerful features of Excel and other spreadsheet programs is the ability to copy a formula or pattern down a column or across a row.

Here's a quick demo showing the scenario you describe for Person A:

enter image description here

For Person B you could just copy columns C and D and paste them over to the right then adjust where the contribution amounts go.

In Excel, you'll notice the cursor change when it is on the bottom right corner of a cell, that's where you click and drag to copy-down or across. To increment the year you don't even need a formula, you can just seed the pattern with some entries, select all of them and copy down. You'll also notice that when I type in the formulas the reference to the interest rate starts off as B1 but then becomes $B$1, 'F4' is the shortcut key that locks the reference, that means when I copy my formula down to subsequent rows it always references B1, and in this case if I copied it to other columns it would also reference B1. $B1 would lock the column reference but allow rows to adjust as it was copied, B$1 would lock row reference and allow columns to adjust as it is copied across (you can hit 'F4' multiple times to cycle through the reference lock variations.

For reference, here are the resulting formulas from the copy-down: enter image description here

You can get much fancier, if you look for loan/mortgage/interest calculator templates in Excel you'll find some that accept a great number of variables and have more dynamic output. Similarly, Excel and most other spreadsheet programs offer a large number of built-in financial functions.

  • 1
    Nice way to illustrate. Now I need to ask a question on superuser.se on how this can be done and embedded in answer :) – Dheer Sep 8 at 7:55
1

For person A rows 1 to 10 are given by the following formula for compounding deposits d over n years at interest rate r.

enter image description here

So with

deposits   d = 1000
interest   r = 0.08

at the end of year 1 (row 1) balance b is

n = 1
b = (d (1 + r) ((1 + r)^n - 1))/r = 1080

through to year 10 (row 10)

n = 10
b = (d (1 + r) ((1 + r)^n - 1))/r = 15645.49

Rows 11 to 47 are calculated by simple compounding, e.g.

n = 1
b (1 + r)^n = 16897.13

...

n = 37
b (1 + r)^n = 269816.22

For person B the formula is used. Rows 1 to 10 are zero. Rows 11 to 47 are

n = 1
b = (d (1 + r) ((1 + r)^n - 1))/r = 1080

...

n = 37
b = (d (1 + r) ((1 + r)^n - 1))/r = 219315.95

It should be simple enough to put these calculations into Excel. E.g.

enter image description here

And an expanded example showing formulas. Row height for rows 23 to 44 is reduced to fit the image. Note d set in the top-left by overtyping 'B2' for variable declaration.

enter image description here

And lastly, the calculated figures, matching the earlier calculations.

enter image description here

Addendum

For those who would prefer to use built-in Excel formulas the same results can be achieved using the future value (FV) function.

enter image description here

  • I haven't gone through the formulae, but I wonder why this answer was downvoted. – Lawrence Sep 7 at 17:16
  • I think someone didn't think it was proper Excel. – Chris Degnen Sep 7 at 17:21
  • 1
    It's not cut-and-paste Excel, granted, but it explains what goes where a lot better than 'proper Excel'. +1 for the effort. – Lawrence Sep 7 at 17:35
  • 1
    I didn't downvote, but the question says "I want 47 rows (65-18) for Person A & B and finally a "total". I want to see it grow in every cell." Looks like those downvotes came when the answer was just the first section without all the rows. Good updates. – Hart CO Sep 7 at 18:30
  • Really you only need row 10 & 47 for A, and just row 47 for B. The other rows just show the balance growing. – Chris Degnen Sep 8 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.