Create an Investment Gain Forecaster using Google Sheets
This post contains affiliate links. This means at no extra cost to you, I may earn a commission if you purchase through my links. Please see my website disclaimer for more info.
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 builtin 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 setup 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
: YearB3
: Contributions / WithdrawalsC3
: Investment DateD3
: Withdrawal DateE3
: Base Interest RateF3
: FrequencyG3
: Effective RateH3
: Beginning ValueI3
: Expected Yearly GainJ3
: 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 bottomright 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 cellE1
, so we can pluck out 2 numbers:
 The $ value in column
H
which is our start value for current year, and 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 usingARRAYFORMULA()
so that we can pass it to theMATCH()
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 theH
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 bottomright 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 cellF4
, we supply that as the frequency to theEFFECT()
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()
andDAYS()
functions. TheDATE()
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 theDAYS()
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 bottomright 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
andJ
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!
Until next time,
michael 😀
Share this post:
Google Apps Script: 2 Caching Methods You Need to Use!
Google Sheets: Use Builtin Functions to Fetch JSON API Data
Using LAMBDAs in Google Sheets to Create Filler Data
Google's Decision to Effectively Killoff Small Sites
Odd Behaviour with Alternating Colors in Google Sheets
How to Redirect URLs with Netlify
Develop Google Apps Script Code in Your Local Code Editor
Limit the Number of Prerendered Pages in Next.js
Build a Weather Widget Using Next.js
Filtering Data in Google Spreadsheets
5 Ways to Redirect URLs in Next.JS
Create a Budget Tool with Google Sheets
Fix Path Name Mismatches in Local & Remote Git Repos
Fix "Cannot find module ..." TypeScript errors in VS Code
Fix "Unknown at rule @tailwind" errors in VS Code
Build a Simple Contact Form with Next.JS and Netlify
Fix "Hydration failed ..." Errors in React
Updating Turborepo to use Yarn Berry
Create an Expensetracker with Google Sheets  Part 2
Create an Expense tracker with Google Sheets  Part 1
Quick and Dirty Portfolio Tracker Part 2  Crypto
Quick and Dirty Portfolio Tracker Part 1  Stocks
Comments