Loans, savings and NPV with Grid's financial functions

Money questions are among the most common reasons people open a spreadsheet. What will this loan cost each month? How much will the savings pot hold in ten years? Is this piece of equipment worth buying? Grid by Zaweba has 55 financial functions built for exactly these sums, and because Grid works offline with no account, your figures never leave your PC.

Loans and savings

The core group works on regular payments at a fixed rate. PMT gives the payment per period for a loan. IPMT and PPMT split any single payment into the interest part and the part that pays down the balance. FV tells you what a series of savings payments will grow to, PV works the other way to give what a future sum is worth today, NPER says how many payments it will take, and RATE finds the interest rate hidden in a set of figures.

The single most common mistake is mixing periods. If payments are monthly, the rate you give must be the monthly rate (the annual rate divided by twelve) and the number of periods must be counted in months. Put the annual rate, the years and the payments per year in labelled cells, and work out the period rate and count in their own cells. The formulas stay readable and you can change one input to compare offers.

The other common surprise is the sign. These functions treat money you receive and money you pay out as opposite signs, so a loan you take in gives a negative payment. That is the convention, not an error. Wrap the result in a minus sign or ABS if you'd rather show a positive figure.

A repayment schedule

A schedule is a tidy job for IPMT and PPMT. Number the periods down a column, then for each row work out the interest and principal parts and the balance left. Drag the fill handle to copy the row down for the length of the loan. The final balance should land on zero, which is a good check that the periods and rate are consistent.

Weighing up an investment

NPV discounts a run of future cash flows back to today's value at a chosen rate. IRR finds the rate at which those flows break even. When the cash flows come on irregular dates, use XNPV and XIRR, which take a column of dates alongside the amounts. MIRR is there for cases where the money you reinvest earns a different rate from the money you borrow.

Lay out the dates in one column and the amounts in the next, with the initial outlay as a negative first entry. That layout suits all of these functions, and it doubles as a record of what you assumed.

Depreciation and bonds

For a small business, depreciation spreads the cost of equipment over its useful life. Grid has the standard methods: SLN for straight line, SYD for sum of years' digits, DB for fixed declining balance, DDB for double declining balance and VDB for a declining balance across any span of periods. Set up one row per year and the charge for each year falls out, ready for your accounts or your accountant.

The family also covers bonds, coupon dates and odd first or last periods, for anyone pricing or checking fixed-income holdings. These take settlement and maturity dates, so enter dates as real dates rather than text.

Making the sheet trustworthy

Use Insert Function to see each function's arguments with a description, and the argument tip as you type to check you're on the right one. Format results as currency so the pennies line up. For a question that runs backwards, such as the deposit needed to bring a payment under a set figure, Goal Seek on the Data tab can work from the answer you want to the input that gives it.

Financial functions in Grid by Zaweba follow the usual spreadsheet conventions, so a workbook built here and saved as .xlsx is ready to share. The sums are only as good as the inputs, though: keep every assumption visible and labelled, and the sheet will still make sense when you come back to it next year.