How to Make a Finance Spreadsheet

Spreadsheet software can be used to create a powerful personal finance management tool. The following instructions can be used to make a personal finance spreadsheet in Microsoft Excel or Open Office. Simply use a comma to separate values...

Method 1 of 3:

Create the Check Register

  1. Picture 1 of How to Make a Finance Spreadsheet
    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. Picture 2 of How to Make a Finance Spreadsheet
    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. Picture 3 of How to Make a Finance Spreadsheet
    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 of 3:

Create the Loan Calculator

  1. Picture 4 of How to Make a Finance Spreadsheet
    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. Picture 5 of How to Make a Finance Spreadsheet
    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.
    1. 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. Picture 6 of How to Make a Finance Spreadsheet
    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. Picture 7 of How to Make a Finance Spreadsheet
    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 of 3:

Create the Loan Payoff Schedule

  1. Picture 8 of How to Make a Finance Spreadsheet
    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. Picture 9 of How to Make a Finance Spreadsheet
    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. Picture 10 of How to Make a Finance Spreadsheet
    Enter the formulas into the corresponding columns of the payoff sheet.
    1. Click in cell B5 and type "=B2" under the Starting Balance heading.
    2. Click in cell C5 and type "=B5*$C$2/12."
    3. Click in cell D5 and type "=$E$2-C5."
    4. Click in cell E5 and type "=B5-D5."
    5. Click inside cell B6 and type "=E5."
    6. Click in cell E6 and type "=B6-D6."
  4. Picture 11 of How to Make a Finance Spreadsheet
    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.
Update 05 March 2020
cellclickformattingtype

You should read it

Maybe you are interested

Category

System

Mac OS X

Hardware

Game

Tech info

Technology

Science

Life

Application

Electric

Program

Mobile