How do I calculate monthly compound interest in Google Sheets?

Question

I have a financial spreadsheet with a column of all my deposits into my savings account. The leftmost column lists the dates of the deposits. Now, let's say my savings balance is $100.00 and my monthly interest is 0.25% (1/4 of 1 percent). This is compound interest paid on the last day of every month. That is, at the end of a month I get 100.00*0.0025 (which is 100.02). At the end of the next month I get 100.02*0.0025. And so on. Is there a financial formula that allows me to calculate this in Google Sheets? I'd prefer to have it all in one cell saying something like "Interest: $-.--" Thanks.

Answer 1

You just use the compound interest formula:

Principle * (1 + Rate / Time) ^ Time

For Cell C2 you want this formula:

=B2*(((1+(D$1/360))^(C$1-$A2))-1)

• Column A is deposit date
• Column B is deposit amount
• Cell C1 is today's date
• Cell D1 is the annual interest rate

Most savings accounts that I know of compound interest daily and credit earned interest monthly, so realistically the above formula will be accurate to today's date, even though you haven't been credited some of the interest yet.

You can also skip the actual compound interest formula and just use the built in future value formula:

=FV(D$1/360,C$1-$A2,0,-B2)-B2

---

To drill down further on only compounding monthly you need to start to play with adjusting the dates...

You can start by taking the two dates and calculating the number of months that have elapsed using the DATEDIF() function like this:

=DATEDIF(A2,C$1,"M")

But you'll have to adjust the two dates because a simple DATEDIFF between today's date and cells A3 and A4 will both return 2 which isn't really right. You could take the first day of the following month of the deposit by using:

=EOMONTH(A2,0)+1

And you can take the first of the month of the current month with

=DATE(YEAR(C1),MONTH(C1),1)

Which makes your formula:

=B2*(((1+(D$1/12))^(DATEDIF(EOMONTH($A2,0)+1,DATE(YEAR(C$1),MONTH(C$1),1),"M")))-1)

But this isn't really right because it doesn't start accumulating interest until the first of the month following the deposit. You could also get a rough number of months by subtracting the two dates and dividing by 30 days.

You can make this more complicated to calculate a number of days for the first month + full monthly interest beyond that but it makes the formula much longer because you'll have

First month in days interest + monthly interest beyond that

To get days remaining in a month you'd do something like:

=EOMONTH($A2,0) - $A2

So to get the proportion of the remainder of the month you do (days occurred in the month divided by number of days in the month):

=(EOMONTH($A2,0) - $A2) / DAY(EOMONTH($A2,0))

Then multiply the above by the monthly interest rate times the principle to get the partial month, then add the monthly interest above.

=($B2*(((EOMONTH($A2,0)-$A2)/DAY(EOMONTH($A2,0))*($D$1/12))))+(B2*(((1+(D$1/12))^(DATEDIF(EOMONTH($A2,0)+1,DATE(YEAR(C$1),MONTH(C$1),1),"M")))-1))

But remember, your principle amount on the monthly interest is now your principle + the interest that was credited in the first month so your formula should actually be:

=($B2*(((EOMONTH($A2,0)-$A2)/DAY(EOMONTH($A2,0))*($D$1/12))))+(($B2*1+((((EOMONTH($A2,0)-$A2)/DAY(EOMONTH($A2,0))*($D$1/12)))))*(((1+(D$1/12))^(DATEDIF(EOMONTH($A2,0)+1,DATE(YEAR(C$1),MONTH(C$1),1),"M")))-1))

At this point you're really splitting hairs because it's the difference of $1.74327 of interest versus $1.74331 when including the first months interest in the principal for the remaining months. This differs from the $1.85 in cell C2 above because you haven't been credited for the first 10 days in August yet. In a lot of cases the minute differences in compounding will only matter on big numbers, and even then.... If you had $10,000,000 in principle, the compounding difference would change from $0.00004 to $4. For most purposes the first formula way up there is more than sufficient (and probably the one I would actually use in all cases because the practical difference in compounding daily versus monthly just isn't significant).

Answer 2

The "Future Value" function does this.

=FV(rate, number_of_periods, payment_amount, present_value, [end_or_beginning])

For example:

=FV(2%, 12, -100, -400, 0)

Note that the payment_amount and present_value should both be entered as negative numbers, otherwise it outputs a negative value

See the Google support article for more info and related functions.