16

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.

17

You just use the compound interest formula:

Principle * (1 + Rate / Time) ^ Time

enter image description here

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).

  • Wow, thanks for the very thorough answer! When I have time I will try this and see if it does the trick. I prefer it to be as accurate as possible, but I also understand that it's usually a matter of pennies, or even less. – grgoelyk Aug 10 '17 at 21:14
  • @grgoelyk, Really, past that break I did it for myself to just go through the motions in excel because sometimes that's fun. Really, the first formula compounds daily and that's what you want. I think the only improvement to make in the first formula is to use the actual appearing number of days in the given year rather than just assuming 360, but again you're just splitting hairs and it will vary by the number of years between the two dates. – quid Aug 10 '17 at 21:19
  • Great answer, and I hate to nitpick, but there are 365 days in a year, not 360, so you need to adjust the formulas above to reflect that. – PopularIsn'tRight Jul 29 at 22:27
  • It's not uncommon for banks to calculate interest based on a 360 day year. And the 5 day difference wouldn't change the outcome until you're dealing in very large numbers. – quid Jul 29 at 22:31
  • @quid Really? Why would banks willingly give away money by doing that? – PopularIsn'tRight Jul 31 at 14:36
8

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.