Browse » Home
Thursday, April 26, 2012
How to Build a Savings Interest Calculator in Excel
1. Open a new spreadsheet in Excel.
2. Label row 1 as follows: A1 is 'Date,' B1 is 'Balance,' C1 is 'Additional deposits' and D1 is 'Interest.' Format columns B, C and D as currency by selecting the columns and clicking on the dollar sign button in the 'Home' tab. (Users of Excel 2003, click the dollar symbol on the Formatting toolbar.)
3. Call your bank or look at your account online to find out when the bank adds interest payments to your account.
4. Fill in column A with interest payment dates, starting with the most recent date your account received an interest payment. If your bank adds interest payments monthly, use monthly dates (October 1, November 1, etc.); if it pays annually, add dates by year. For example, if your last interest payment was October 1, 2009, the next date will be October 1, 2010.
5. Type the current balance of your savings account into cell B2.
6. Fill in column C with additional deposits. If you deposit $500 into the account in the interval between interest payments, enter '500' in cells C2, C3, and down as far as you'd like. If you deposit irregularly, leave those cells blank but enter figures each time you make a deposit. You can combine these approaches, entering a minimum figure and adding to it if you make more deposits.
7. Enter the following calculation in cell D2, without the quotation marks: '=B2 C2 x*(B2 C2)' where x is the interest rate. The interest rate type--daily, monthly, annually or other--must match the intervals between the dates in column A (step 4). Remember to multiply the interest rate expressed as a percentage by .01. So a 1.5 percent interest rate calculated once a year gives you a multiplier of 0.015. In this case your formula would be '=B2 C2 .015*(B2 C2)'. If the same interest rate was applied monthly instead of annually, your formula would be '=B2 C2 (0.015/12)*(B2 C2)'.
8. Copy cell D2 and paste in cells D3, D4 and on down as far as you'd like.
9. Navigate to cell B3 and type (without the quotation marks) '=D2'. This will cause Excel to display the formula result--your account balance after the interest payment--in cell B3.
10. Copy cell B3 and paste in cells B4, B5 and on down as far as you'd like.
11. Save the spreadsheet and close it.