Create an Investment Gain Forecaster using Google Sheets

Google Sheets is a powerful tool for creating financial models and forecasts, but even for simpler things like making a forecast of your potential portfolio gains over the years.

Google Sheets has tons of built-in functions for calculating interest, like the FV() and PV() functions, as well as the PMT() and ACCRINT() functions.

The common thing with all of these functions however is that they produce (output) a single value, and not a forecast of how your investment will grow over time, which is what we’ll be building in this tutorial.

They’re also more complex to use, requiring you to know the exact values for the interest rate, the number of periods, and the payment amounts, which is not something I’m utilizing for this tutorial.

For this tutorial, we’ll be using the EFFECT() function to calculate the effective annual interest rate, as it’s a much simpler function, and then building a simple table to forecast the growth of an investment over a number of years.

Our final layout will look something like this:

Getting Started

Note: If you prefer to just download a copy of my already formatted and set-up sheet instead of creating your own, you can simply click here to copy my template.

First off, we’ll need to create a new Google spreadsheet. So head over to drive.google.com, and create a new spreadsheet (either directly in My Drive, or in a folder in My Drive, whichever you prefer).

Set up the Basic Layout

Now that we’ve got a blank sheet ready, we’ll start by adding in the first 2 header rows, with some basic labels for our columns.

Adding the Header Rows

Add the following text to the indicated cells:

• A1: Initial Value:
• D1: Current Value:
• H1: Final Value:

And then, skipping to the 3rd row, add the following text to cells A3 through J3:

• A3: Year
• B3: Contributions / Withdrawals
• C3: Investment Date
• D3: Withdrawal Date
• E3: Base Interest Rate
• F3: Frequency
• G3: Effective Rate
• H3: Beginning Value
• I3: Expected Yearly Gain
• J3: Average Monthly Gain

Note: I’ve labeled cell H3 as “Beginning Value” to highlight the fact that it’s the value at the beginning of the year, and not the end. I’m not actually showing the value at the end of the year, as that would be the same as the value at the beginning of the next year. You can simply label it as “Value” if you prefer.

Setting up the Year Column

Column A, starting from cell A4, is where we’ll be adding the year markers. I’m staring mine with the year “2023” in cell A4, but you can start with the current year, or a prior year if you have an investment you started in the past.

For the rest of the cells in column A, we’ll create a simple formula to show nothing if no year was entered in cell A4, and if a year was entered, it will add 1 to the previous year.

So, in cell A5, enter the following formula:

=IF(A4="","",A4+1)

Then click and drag the bottom-right corner of cell A5 down to whichever cell you want to stop at (I’m stopping at A23).

With just some basic formatting, your sheet should now look similar to this:

Adding in the Formulas

Now it’s time for the magic!

We’ll start by adding the formulas for the Initial Value:, Current Value, and Final Value sections, and then we’ll move on to the Effective Rate column.

Initial Value Section

In cell B1, to get our investment’s initial value, enter the following formula:

=B4

Current Value Section

In cell E1, to get our investment’s current value, enter the following formula:

=IFERROR(VLOOKUP(YEAR(NOW()),A4:H,8)+(VLOOKUP(YEAR(NOW()),A4:J,10)*(MONTH(NOW())-1)),"N/A")

And then in cell F1, to get the percentage gained thus far, enter:

=IFERROR(E1/B1,"N/A")

Explanations: We’re using the VLOOKUP() function twice in cell E1, so we can pluck out 2 numbers:

1. The $ value in column H which is our start value for current year, and
2. The $ value in column J which is the expected monthly gain for the current year.

We then multiply the expected monthly gain by the number of months that have passed in the current year, and add that to the start value to get the current value.

Finally, we’ve wrapped it in an IFERROR() function to display “N/A” if there’s an error (like if the year or $ values aren’t found in the table).

Final Value Section

Next up, in cell I1, enter the following formula:

=IFERROR(INDIRECT("H"&(2+MATCH(TRUE,ARRAYFORMULA(ISBLANK(H4:H)),0)))+INDIRECT("I"&(2+MATCH(TRUE,ARRAYFORMULA(ISBLANK(H4:H)),0))),"N/A")

And then in cell J1, enter:

=IFERROR(I1/B1,"N/A")

Explanations: This formula is a bit more involved than the one in cell E1, but it’s basically attempting to find the last year in the table that has a value in the Beginning Value column, and then adding the Expected Yearly Gain to that value to get the final value.

Starting from the inner part of the left side of the formula, we’re using ISBLANK() to find the first blank/empty cell in the Beginning Value column, but then expanding out the result using ARRAYFORMULA() so that we can pass it to the MATCH() function to find the offset of the first blank cell.

Because our MATCH() function is only getting values from starting from row 4, we need to add 2 to it to get the actual row in the sheet. Note that we’d actually need to add 3 to get the correct row of the 1st blank cell, but because we actually want the row of the last filled cell, we add 2 instead.

We then use INDIRECT() to create the cell reference we need for the H column to generate an actual value. We do the same for the Expected Yearly Gain column, and then add the 2 values together to get the final value.

Finally, we’ve once again wrapped it in an IFERROR() function to display “N/A” if there’s an error.

Effective Rate Column

The “effective rate” is basically the annual interest rate, but includes the effect the compounding has on the base rate, assuming the frequency is set to more than 1. If you’re interested in the math behind it, you can check out this Wikipedia page.

In cell G4, enter the following formula:

=IFERROR(EFFECT(E4,IF(F4="",1,F4))*(DAYS(IF(D4,D4,DATE(A4,12,31)),IF(C4,C4,DATE(A4,1,1))) / DAYS(DATE(A4,12,31),DATE(A4,1,1))),"N/A")

And then click and drag the bottom-right corner of cell G4 down to whichever cell you want to stop at (again, I’m stopping at row 23).

Explanations: We’re using the EFFECT() function here to calculate the effective annual interest rate, and then multiplying it by the fraction of the year that the investment was held for. If there’s a compounding frequency in cell F4, we supply that as the frequency to the EFFECT() function, otherwise we use 1 (which is the same as no compounding for the year).

Now to calculate the actual portion of the year the money is invested, We’re using the DATE() and DAYS() functions. The DATE() functions come in to play if we’ve left the Investment Date or Withdrawal Date cells blank, otherwise we use the dates in those cells. We then feed those dates into the DAYS() function to get the number of days between the 2 dates, and then divide that by the number of days in the year to get the fraction of the year the investment was held for.

The Remaining Formulas

For the next 3 columns - Beginning Value, Expected Yearly Gain, and Average Monthly Gain - enter the following formulas in the noted cells:

• H4:

  =IFERROR(IF(ISTEXT(I3),IF(ISBLANK(B4),"N/A",B4),(H3+I3)+B4), "N/A")
  

• I4:

  =IFERROR(H4*G4, "N/A")
  

• J4:

  =IFERROR(I4/12, "N/A")
  

And then click and drag the bottom-right corner of each of the cells down to whichever cell you want to stop at (as before, I’m stopping at row 23).

Explanations: The formulas in columns I and J are pretty basic math, so I won’t bother explaining those.

For the formula(s) in column H, we’re first checking if the value in the previous row is text (which would likely mean it’s the header row), and if it is, we’ll pull the amount in the Contributions / Withdrawals column if there is one. If the previous row doesn’t contain a text value, we’ll add the Beginning Value and Expected Yearly Gain from the previous row, and then add the Contributions / Withdrawals from the current row to that.

Once again, we’ve wrapped the formulas in an IFERROR() function to display “N/A” if there’s an error.

Formatting the Sheet

Now that we’ve got all the formulas in place, we can do some basic formatting to make the sheet look a bit nicer.

I’ll leave it up to you as to what exact formatting you want to apply, but you’ll probably want to at least apply currency formatting to any columns with $ amounts, and percentage formatting to any columns with percentages.

Here’s what mine looks like after some formatting:

Adding in Some Sample Data

Finally, you can add in some sample data to see how the sheet works. Of course if you’ve already got an investment in place that you want to forecast, you can use that data instead.

With some sample data added, here’s what my sheet looks like now:

Things to Note

This sheet is far from perfect, as it’s difficult to account for all the possible scenarios that could occur with an investment, but it should give you a good starting point for creating your own investment gain forecaster.

The idea is really just to give you a rough idea of how an investment might grow over time, and not to give you an exact figure.

Notably, the sheet doesn’t account for:

• When the contributions/withdrawals are actually made. The Effective Rate column is using the Investment Date and Withdrawal Date columns to calculate the fraction of the year the investment was held for, but it’s not aware of whether or not the contribution/withdrawal was made before or after a compounding period. It’s simply applying the calculated effective rate across the entire year.

• The Contributions / Withdrawals column, if populated, always behaves as if the money was added/withdrawn at the beginning of the year. This is not the case in reality, but it’s a simplification I’ve made to keep the formulas simpler.

• The Withdrawal Date column won’t stop the values from cascading down the sheet if you don’t include a 100% withdrawal amount (as a negative value) in the Contributions / Withdrawals column. You could add a check to the Beginning Value column to stop the cascade, but again, I’ve left it out to keep the formulas simpler.

Conclusion

That does it for this tutorial. I hope you find it useful, and that you’re able to use it to create your own investment gain forecaster.

As I noted above, this is just a starting point, and there are many ways you could expand on it to make it more useful for your specific needs. When it comes down to it, the future hasn’t yet been written, so any forecasting, no matter how accurate, is really just a guess.

Despite that, I still find it useful to have a rough idea of how an investment might grow over time, and I hope you do too.

If you ran into any issues while building your sheet, feel free to copy my template and have a look to see what might be causing the issue.

If you have any questions, or if you have any suggestions for how to improve the sheet, feel free to leave a comment below!