williamwallace

New Member
Joined
Jun 12, 2020
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,018
Office Version
  1. 2010
Platform
  1. Windows
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
ABCD
2Initial$100,000.00
3Term (yrs)10
4Monthly rate1.0000%12.6825%annl rate
5Monthly contrib$100.00
6Annl bonus$5,000.00
7FV$443,733.92
Sheet1
Rich (BB code):
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.

Rich (BB code):
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,633
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCD
1Principal$20,000.00$58,193.70
2Contributions mo100.00
3Annual5,000.00
4Rate4%
5Term5
8d
Cell Formulas
RangeFormula
D1D1=-FV(B4/12,B5*12,B2,B1,1)-FV(EFFECT(B4,12),5,5000,0,0)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,018
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 12, 2020
Messages
3
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Mar 2, 2014
Messages
3,018
Office Version
  1. 2010
Platform
  1. Windows
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 (!)
 
Last edited:

williamwallace

New Member
Joined
Jun 12, 2020
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
No this is good enough. I can align cash flows to April. Thank you both again!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,927
Messages
5,627,675
Members
416,264
Latest member
Dezmo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top