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
- 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$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.
- 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
- 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.
Method 3 of 3:
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$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."
- 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.
4 ★ | 1 Vote
You should read it
- How to use conditional formatting in Microsoft Excel 2016
- How to Round in Excel
- How to Subtract in Excel
- 4 ways to quickly check the partition type on Windows 11
- Diagonal lines in a cell in Excel
- How to name, comment and protect cells in Excel
- Trick to remove Formatting text format in Word
- How to format data based on other cell conditions in Excel
- How to Preserve Formatting When Using Copy and Paste
- How to use conditional formatting in Numbers on Mac
- How to automatically create valuable cell borders in Excel
- How to use Conditional Formatting to conditional formatting in Excel
Maybe you are interested
Transparent solar cells help smartphones charge themselves with sunlight
Tips for formatting cells in Excel for professional spreadsheets
How to Justify Text in Cells on Excel - Adjust Text Spacing
What is a bifacial solar cell? What are the advantages?
Successfully developed 'indoor solar cells' with conversion efficiency up to 37%
How to name an Excel cell or data area - Define Name feature on Excel