0

I'm trying to figure out how to write a formula and/or use a function to calculate the following in Google Sheets.

John deposits $1,000.00 into a savings account on Dec 31st. John also deposits $100 on the 1st of every month and will do so for the next 10 years. The interest rate is fixed @ 4% for 10 years and compounds monthly. How much will John have after ten years?

$1,0000 principal. 
$1,200 per year for 10 years.
Total deposits $13,000.

I currently have everything figured out with the exception of the monthly contribution. Screenshot of the data is below. How would I update to include the monthly $100 contribution? Thanks for your help!

=C6*(1+C7/C9)^(C9*C8)

enter image description here

This solution worked, =fv(C7/C9,C8*C9,-C10)+C6*power(1+C7/C9,C8*C9)

Improve this question

1 Answer 1

Reset to default
1

You can simply treat this as two different investment and just sum them. 1000$ one time at 4% plus 100% per month at 4% both for 10 years.

Final value would be =FV(C7/C9,C8*C9,-C10)+C6*power(1+C7,C8) or $16,205.22

Improve this answer
3
  • Thank you for your answer. I compared the result to investor.gov/financial-tools-calculators/calculators/… and noticed a discrepancy of roughly $10. Do you know what would cause this?
    – Mr. B
    Commented Oct 22, 2022 at 0:15
  • Found it, =fv(C7/C9,C8*C9,-C10)+C6*power(1+C7/C9,C8*C9). Needed to update the 2nd formula to include monthly compounding).
    – Mr. B
    Commented Oct 22, 2022 at 0:30
  • 1
    Do you mind posting which row is #7 in your screen print?
    – Bernie Lenz
    Commented Apr 9 at 13:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .