Multiply on a Specific Date (investment simulation for students)

Question

I am trying to create an investment simulation for my high school students in Google Sheets. They already have a bank account that they use (in google sheets) for reward for specific behaviors. I would like to teach them about using investment vehicles while using the current system I have for rewarding their behaviors- but I do not want to have to update it every week/month.

I would love to find a formula that multiplies a specific cell by x% every Monday (or any day), then adds the product to the original number. Is there a way to do this in Google Sheets? I have been searching for a few days, but I cannot find this information. (I know investments are based on monthly, but with only having the students for a year, I will not be able to show them how powerful exponential growth can be unless I exaggerate it, which I will clearly explain to them that it is an exaggeration.)

Answer 1

This should work for you:

=FV(2.5%,ROUNDDOWN(DATEDIF("01/01/21",TODAY(),"D")/7),0,-100)

FV calculates future value of an investment.

Parameter 1 is the interest rate, which can be entered as you see or as a decimal (e.g., .025).

Parameter 2 is number of periods. I used an embedded calculation for this, taking the difference between your start date and the current "today" date, counted in days ("D"), divided by 7 (days a week) and rounded down. If your initial date in quotes is a Monday, each "next period" should also occur on a Monday, etc.

Parameter 3 is any regular additional payment to the account that will be made per period (in your case, weekly, which I assume is zero).

Parameter 4 is the initial investment amount (i.e., principle), expressed as a negative number (since you "lose" that money when investing it and "gain" an amount when withdrawing or when it matures).

Any of the parameters can be cell references. So if you want to play around with different interest rates (say, in cell A1) and investment amounts (say, in cell A2), you could do this:

=FV(A1,ROUNDDOWN(DATEDIF("01/01/21",TODAY(),"D")/7),0,-1*A2)

In this case, you'd enter the investment amount in A2 as a positive number; then the formula would apply the negative element.