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 in a formula when using Excel, and use a semicolon to separate the values in a formula when using Open Office. This article provides step-by-step instructions on how to make a finance spreadsheet that includes a check register, a loan calculator and a loan payoff sheet.
Create the Check Register
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.
Enter the register formula.Type the following formula into cell H20: =SUM($E)-SUM($D). 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.
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.
Create the Loan Calculator
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.
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.
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.
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.
Create the Loan Payoff Schedule
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.
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.
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/12."
- Click in cell D5 and type "=$E-C5."
- Click in cell E5 and type "=B5-D5."
- Click inside cell B6 and type "=E5."
- Click in cell E6 and type "=B6-D6."
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.
Video: #1 Best Budget Spreadsheet | Simple, Clean, Colorful, Easy to Use
5 Tips For Sevens Survival
Constipation Relief: Home Remedies And More
The Royal Baby’s Name Has Been Announced Meet Princess Charlotte ofCambridge
Radishes with Pea Puree
How to Apply Eyeshadow
Cute Braided Bun Hairstyle Tutorial
The One Thing: Jouer Luminizing CheekTint
Watch Jimmy Fallons Epic A Cappella Medley Of Star Wars Songs With The Cast
How to Donate Food
3 Sites forSupporting Breast Cancer Awareness
21 Stylish Ombre Color Ideas for Brunettes
You Might Already Own The Most-Pinned Summer Fashion Trends of2017
If your partner has this trait, you’re probably less likely to orgasm
33 Hottest A-Line Bob Haircuts You’ll Want to Try in 2019