Building Marcus Savings Calculator in Excel

Question

I'm trying to do some analysis in Google Sheets which involves putting money in Marcus' High Yield savings account. For some reason, I can't seem to get my numbers to match the values from their savings calculator.

I'm using the value of interest rate to be 2.25%, compounded daily as mentioned here, and the principal being $10000, with $100 being deposited every month for a total period of 2 years.

As per their calculator, this results in an interest of $507, whereas with the below formula, I'm getting $12949 or $549 as the interest using the formula below.

FV(2.25%/365, 365 * 2, -100/30, -10000, 0) - 10000 - 2400

Any idea what I'm doing wrong?

Answer 1

The 2.25% is an annual effective rate (same as APY)

e = 0.0225

Converting the annual effective rate to a monthly rate

r = (1 + e)^(1/12) - 1 = 0.00185594

Compounding the principal, with n = 24

10000 (1 + r)^n = 10460.2625

Compounding the payments, with d = 100 and payment at month-end.

fv = (d ((1 + r)^n - 1))/r = 2451.9379

Adding together

2451.9379 + 10460.2625 = 12907

In Excel

=FV(r, n, -d, -10000)

=FV(0.00185594, 24, -100, -10000)

£12,906.99

interest = 12907 - 10000 - 2400 = 507