How to write a formula using Apple Spreadsheet Number related to savings compound interest

How to write a formula using Apple Spreadsheet Numbers where the initial savings account deposit is $30,000.00 with a subsequent monthly deposit of  $1,000.00, an interest rate of 3.90% compounded daily.

MacBook Pro 16″, macOS 15.2

Posted on Jan 17, 2025 7:59 PM

Reply
8 replies
Sort By: 

Jan 19, 2025 6:35 PM in response to tcwestover

Because you have a different compounding period (daily) from cash flow intervals (deposits) the stock formulas don't fit your situation. But spreadsheets are ideal for solving this kind of problem. Set up a simple table with each row being a day, tracking the beginning value for that day (which equals the ending value of the previous day), adding any deposits plus interest, and calculating an ending balance for that day.


The table can look something like this (I've hidden rows in the middle so it could be posted here):



The interest rate (I assumed you meant 3.9% per annum, but compounded daily on a 365-day year basis) is entered in E1.


The formula in E2, filled down:


=SUM(C2:D2)*E$1/365


The formula in F2, filled down:


=SUM(C2:E2)


The formula in C3 filled down:


=F2


Enter the $1000 deposits in the rows representing the last day of each month (if the deposits are at the beginning of the respective days; otherwise enter on the following day).


Row 367 is defined as a Footer Row so it can hold formulas that refer to the columns above.


In D367:


=SUM(D)


In E367:


=SUM(E)


In F367:


=SUM(D$367:E$367)


You should get results like those shown. By the end of 1 year you will have put $42,000 in, and earned $1,412.58 in interest if you started January 1 of a non-leap year. In finance there are endless small variations in possible assumptions that can yield slightly different results. But this approach should give you a good approximation.


SG


Reply

Jan 18, 2025 11:39 AM in response to SGIII

Thank you for your response. I want to know my total savings where I have initially deposited $30,000.00 and then deposit $1000.00 per month with a daily compound interest rate of 3.90% for one year. (So I would have at least $42,000.00 plus the compounding interest. It's the compounding interest amount I don't know how to figure with the scenario I have presented. Thank you in advance for your assistance and cooperation. I truly appreciate it.

Reply

Jan 18, 2025 9:58 PM in response to tcwestover

So you make a $30,000 deposit on day 1. And your total period is, say, 365 days. When will you be making the monthly deposits? That makes a differences. At the beginning of each month, in which case the first deposit will by $31,000? In the middle of the month? Or at the end of each month in which case your last $1,000 deposit will be on the last day of the yearly period and therefore will not have earned interest?


SG

Reply

How to write a formula using Apple Spreadsheet Number related to savings compound interest

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.