Grow Only Part

Topic

New Reply

I need to add a fixed amount to an amount that grows by 4% each year.

For instance A1= 10,000 and A2 = 3,000

The total in year 1 is 13,000 but in year 2 it is A1+ (A2*1.04). In year 3, its A1 + (A2*1.04*1.04)etc.

Is there a function that would calculate this or do I just keep adding a *1.04 to each next year.

Reply | Quote

Replies

If, in column B, you put the date that you start counting then:

=IF(TODAY()-B2<365,$A$1,$A$1+$A$2*(YEAR(TODAY())-YEAR(B2)-1))

should do it.

Reply | Quote

Thanks for the help but I think I wasn’t too clear on what I was trying to do. I’m not concerned with calculating time but rather incrementing the sum of 2 cells by 4% each year while also adding a base amount. The formulas I am using for year 1, 2, and 3 are below. I’m thinking there is a function I could use that would treat the amount that is growing (cells D10-11) like an annuity that earns interest (4%) AND would also let me add on the base amount (cell D9)

=SUM($D$10:$D$11)*1.04+SUM($D$9)
=SUM($D$10:$D$11)*1.04*1.04+SUM($D$9)
=SUM($D$10:$D$11)*1.04*1.04*1.04+SUM($D$9)

While my formula works, it seems rather unsophisticated.

Reply | Quote

Actually, I wasn’t calculating the time. I was just finding how many year had elapsed from the time your process start and multiply that by your percentage increase. Do you try it?

Reply | Quote

I see now. Thanks for your help.

Reply | Quote

In cell B2 enter the percentage growth you are planning for, and enter the annual contribution you are going to make.
Leave row 4 blank and enter the following formula in cell B5. Copy the formula aas many rows down as you like – you could also place meaningful labels in column A – like ‘growth,’ ‘contribution,’ and the year.

=B4 * (1 + $B$2) + $B$3

It will be easier to understand in a month if you name the cells B2 and B3, and ‘apply’ names to the formulas in B5:B?

Reply | Quote

Thanks. And range names do help!

Reply | Quote

Sherry,

One of Excel’s math operators is the exponentiation operator. Simply put, it raises a number to a power. Even more simply put, it multiplies a number by itself until you’ve done it the number of times given by the power.

For example, 2 to the power of 3 is 2x2x2=8.

In Excel, you’d write a formula as 2^3 (the symbol between the 2 and the 3 is shift on the 6 key). This operator has even higher precedence than multiply * and divide /.

Now applying this to your problem:
– $D$10:$D$11 are the sum of what you want raised to the power
– $D$9 is the constant adder
– put the percent growth in some cell, as suggested – call it $E$1

Then I’d set up the following – I’ll use col A and B, starting in row 1, but you can use whatever you want;
YEAR AMOUNT
1 =(SUM($D$10:$D$11)*((1+$E$1)^A2)) + SUM($D$9)
2 = [just drag the formula from B2]
Note the only thing that changes in the above formula as you drag it is A2 changes to A3 to A4 to … Since A2 references the year, the year then becomes the “power” in the formula, which means you are multiplying the % up to the power given by the year.

Hope this helps (and hope I didn’t screw up on the parens).

Fred

Reply | Quote

Fred,

The formula works like a charm!!

Thanks.

Reply | Quote