Cell: How to Make a Finance Spreadsheet

This updated guide examines Cell: How to Make a Finance Spreadsheet and organizes the essential facts, background, and practical takeaways in clear American English.

Method 1

Create the Check Register

1. Format the column headings. Type "Transaction date" into cell B19, "Description" in cell C19, "Purchase" in cell D19, "Deposit" in cell E19 and "Balance" in cell H19. Then select cells B19 through H19, and click the word wrap button on the formatting tool bar.
2. Enter the register formula. Type the following formula into cell H20: =SUM($E$4)-SUM($D$4). Position the mouse pointer in the lower right corner of cellH20 and use the Fill handle (+) to copy the formula from H20 down through H44.
3. Set the numerical formatting. Select cells D20 through D44, and click the currency ($) option from the number formatting menu. Do the same for cells H20 through H44. The check register balance will automatically update with each new entry.

Method 2

Create the Loan Calculator

1. Enter the column headings. Type "Loan Amount" in cell B1, "Interest Rate" in cell C1, "Term of loan" in cell D1 and "Payment" in cell E1.
2. Set the numerical formatting. Click in cell B2, and select the currency ($) option from the number formatting menu located on the formatting toolbar. Any value entered in cell B2 will now appear as a dollar amount.
   • Click in cell C2 and select the percentage (%) option from the number formatting menu on the toolbar. Any value entered in cell C2 will now appear as a percentage.
3. Format the borders. Click in cell B1, and drag to select cells B1 through E2. On the formatting toolbar, click the arrow on the Borders button and select "All borders" from the available options.
4. Enter the formula in the monthly payment field. Click in cell E2 and enter the following formula: =PMT(c2/12,d2*12,-b2) and press enter. Now select the currency ($) option from the number formatting menu located on the formatting toolbar. The amount of the monthly payment will now appear in cell E2 and will be represented as a dollar amount.

Method 3

Create the Loan Payoff Schedule

1. Enter the column headings for the payoff schedule. Type "Payment Date" in cell A4, "Starting Balance" in cell B4, "Interest paid" in cell C4, "Principal Paid" in cell D4 and "Ending Balance" in cell E4. Select cells A4 through E4 and click the Word wrap button on the formatting tool bar.
2. Enter the payment dates. Click in cell A5 and enter the date of the first payment. Position the mouse pointer in the lower right corner of cell A5. The Fill handle (+) should now be visible. Click and drag the fill handle from cell A5 to cell A17. The payment dates for the next 12 months should be visible in the payment date column.
3. Enter the formulas into the corresponding columns of the payoff sheet.
   • Click in cell B5 and type "=B2" under the Starting Balance heading.
   • Click in cell C5 and type "=B5*$C$2/12."
   • Click in cell D5 and type "=$E$2-C5."
   • Click in cell E5 and type "=B5-D5."
   • Click inside cell B6 and type "=E5."
   • Click in cell E6 and type "=B6-D6."
4. Copy the formulas down each column. Finally, position the mouse pointer in the lower right corner of cell B6 and use the Fill handle (+) to copy the formula from B6 through B17. Repeat this process for cells C6, D6 and E6. The payoff sheet formulas have been entered and the finance spread sheet is ready to use.