calculating FV of an investment

[williamwallace]

Hi all, quick question.

I am looking to calculate the future value (FV) of an investment. I have a lump sum to start with, a known rate of return and I would be making consistent, monthly contributions ($100). However, I would also like to add a large payment once a year into the investment portfolio (e.g. $5000 cash from a bonus).

Does anyone know how I could build a formula to include both the consistent $100 monthly contributions and the annual $5000 cash bonus? I tried setting them up as two separate calculations and adding them together but I know that's incorrect as I'm missing out on interest compounding.

Appreciate any thoughts and thanks in advance.

 

[joeu2004]

I assume that monthly contributions are at the end of the month, and annual bonuses are at the end of the year. I also assume that the term of the investment is a number of years.

The key is to calculate the contributions compounded over the year, and to add them at them at the end of the year together with the bonus.

Thus, referring to the worksheet below:

=FV(C4, B3, -B6-FV(B4, 12, -B5, 0), -B2)

where C4 is the annual rate, B3 is the term (years), B6 is the annual bonus, B4 is the monthly rate, B5 is the monthly contribution, and B2 is the initial balance (lump sum).

If monthly contributions are at the beginning of the month, use FV(B4, 12, -B5, 0, 1).

Book1
	A	B	C	D
2	Initial	$100,000.00
3	Term (yrs)	10
4	Monthly rate	1.0000%	12.6825%	annl rate
5	Monthly contrib	$100.00
6	Annl bonus	$5,000.00
7	FV	$443,733.92
Sheet1

Formulas:
C4: =(1+B4)^12-1
B7: =FV(C4, B3, -B6-FV(B4, 12, -B5, 0), -B2)

That assumes the monthly rate of return is given in B4.

If the annual rate of return is given in C4 instead, the compounded monthly rate in B4 is the formula =(1+C4)^(1/12) - 1.

The following amortization schedule demonstrates the correctness. Note that H122 is the same as B7.

Book1
	E	F	G	H
1		Contrib (end month)	Bonus (end year)	End Bal
2				$100,000.00
3	1	$100.00		$101,100.00
4	2	$100.00		$102,211.00
5	3	$100.00		$103,333.11
6	4	$100.00		$104,466.44
7	5	$100.00		$105,611.11
8	6	$100.00		$106,767.22
9	7	$100.00		$107,934.89
10	8	$100.00		$109,114.24
11	9	$100.00		$110,305.38
12	10	$100.00		$111,508.43
13	11	$100.00		$112,723.52
14	12	$100.00	$5,000.00	$118,950.75
111	109	$100.00		$392,210.85
112	110	$100.00		$396,232.96
113	111	$100.00		$400,295.29
114	112	$100.00		$404,398.25
115	113	$100.00		$408,542.23
116	114	$100.00		$412,727.65
117	115	$100.00		$416,954.93
118	116	$100.00		$421,224.48
119	117	$100.00		$425,536.72
120	118	$100.00		$429,892.09
121	119	$100.00		$434,291.01
122	120	$100.00	$5,000.00	$443,733.92
Sheet1

Formulas:
E3: =E2+1
F3: =$B$5
G3: =IF(MOD(E3,12)=0, $B$6, "")
H3: =H2*(1+$B$4)+F3+N(G3)
Copy E3:H3 into E4:H122

If monthly contributions are at the beginning of the month, the formula in H3 is =(H2+F3)*(1+$B$4)+N(G3)

 

[Dave Patton]

The following may provide some ideas.
Consider carefully when you make the payments.
You can build a table to check the total.
Check Excel's help for information on the functions.

T202006a.xlsm
	A	B	C	D
1	Principal	$20,000.00		$58,193.70
2	Contributions mo	100.00
3	Annual	5,000.00
4	Rate	4%
5	Term	5
8d

Cell Formulas
Range	Formula
D1	D1	=-FV(B4/12,B5*12,B2,B1,1)-FV(EFFECT(B4,12),5,5000,0,0)

 

[joeu2004]

With errata....

=-FV(B4/12,B5*12,B2,B1,1)-FV(EFFECT(B4,12),B5,B3,0,0)

where B1 is the initial balance (lump sum), B2 is the monthly contribution (at the beginning of the month), B3 is the annual bonus (at the end of the year), B4 is the annual (interest) rate, and B5 is the term (years).

=FV(C4, B3, -B6-FV(B4, 12, -B5, 0), -B2)

where C4 is the annual rate, B3 is the term (years), B6 is the annual bonus (at the end of the year), B4 is the monthly rate, B5 is the monthly contribution (at the end of the month), and B2 is the initial balance (lump sum).

You can rewrite my suggestion in the form of Dave's suggestion, if you prefer, to wit:

=FV(B4,12*B3,-B5,-B2,0) + FV(C4,B3,-B6,0,0)

The key difference is whether the (annual) "rate of return" is treated as a compounded yield (as I assumed) or as a simple interest rate (as Dave assumes).

Both assumptions are equally valid. It depends on the type of investment(s).

 

[williamwallace]

Wow thank you both so much, this is excellent. I should have provided more clarity (so please accept my apologies)

This is an equity investment without any withdrawals so the rate of return would be compounded.

Also, yes, monthly contributions are made at the beginning of each month but the bonus is actually contributed in March (not at end of year). Does that change anything?

Thank you again.

 

[joeu2004]

This is an equity investment without any withdrawals so the rate of return would be compounded. Also, yes, monthly contributions are made at the beginning of each month [....] Does that change anything?

Yes. I explained all the necessary changes in my previous suggestions. To summarize (referring to my example):

=FV(C4, B3, -B6-FV(B4, 12, -B5, 0, 1), -B2)

or if you prefer Dave's form (in the context of my example):

=FV(B4, B3*12, -B5, -B2, 1) + FV(C4, B3, -B6, 0)

It is the addition of the 5th parameter ("type") for FV and setting it to 1 for (only) the calculation of the monthly contribution that makes the difference.

Also, to check with my amortization schedule, H3 should be:

=(H2+F3)*(1+$B$4) + N(G3)

In other words, the monthly contribution is added to the previous balance, then monthly interest is applied.

Finally, to reiterate, if the rate of return is specified as an annual rate in C4, the monthly rate in B4 is =(1+C4)^(1/12) - 1, not B4/12.

OTOH, if the rate of return is specified as a monthly rate in B4, the annual rate in C4 is =(1+B4)^12 - 1, ,as shown in my example. It is not B4*12.

but the bonus is actually contributed in March (not at end of year).

The "year" should be 12 months starting with April. Thus, the bonus is indeed at the end of "the year" (12-month period).

If you cannot align your annual cash flows with April, then we must adjust the formulas to include "odd periods": first and/or last.

In order to assist you with that, we need more information. Provide the following dates:

1. First and last monthly contribution
2. First and last annual bonus
3. First and last date of the annuity, if that does not align with #1 or #2 (!)

 

[williamwallace]

No this is good enough. I can align cash flows to April. Thank you both again!