Microsoft Excel is the most popular spreadsheet program used worldwide but there are some features within Excel which tend to be overlooked even by “power-users”. People who swear by macros, pivot tables, V-Lookups and H-Lookups are unaware of the existence of Spreadsheet Solutions within Microsoft Excel and how to make use of it.
Microsoft Excel provides pre-defined solution templates for:
– Loan Amortization
– Personal Monthly Budget
– Billing Statement
– Blood Pressure Tracker
– Expense Report
– Sales Report
– Time Card
While a few of them may not be applicable to everyone, such as a blood pressure tracker, solutions such as Loan Amortization and Personal Monthly Budget can prove useful to almost everyone at some point.
Loan Amortization using Microsoft Excel
Loan amortization refers to the repayment schedule comprising of principal and interest payments against a loan for a lump sum. It is considered to be one of the simplest repayment models, as payments are considered to be equally divided across the loan period and each payment is split into a principal and an interest component. A point to note here is that a greater portion of the repayment goes towards the interest at the beginning of the amortization schedule and more money goes towards the principal towards the end.
A loan amortization schedule can be prepared using a pre-defined template within Microsoft Excel. The steps to be followed in order to create a loan amortization schedule are
– Open Microsoft Excel and Create a New File
– Right Click on a Sheet Name (E.g. Sheet1) at the bottom left of the screen
– Choose Insert Spreadsheet Solutions (2nd Tab)
– Select Loan Amortization and Click OK.
A blank loan amortization template opens up.
This template can be used to determine your loan amortization schedule by entering the following parameters.
– Loan Amount
– Annual Interest Rate
– Loan period in years
– Number of payments per year
– Start date of loan
– Optional Extra Payments
Personal Monthly Budget using Microsoft Excel
Personal Monthly Budget is a spreadsheet solution provided by Microsoft Excel which helps you to estimate and track your monthly incomes and expenses versus your actual. This can be a very effective tool to aid your personal financial planning.
Various pre-defined categories exist, such as:
-Savings or Investments
-Gifts and Donations
Each category is broken down into various line items. For e.g. Housing comprises of Mortgage or Rent, Phone, Electricity, Gas, Water & Sewer, Cable, Waste Removal, Maintenance or Repairs, Supplies and Others.
Within each category and line-item combination, there is a projected cost and an actual cost entry along with variance. This helps to determine your monthly budgets and can help manage your finances better.