OmniDocExcel Formulas for Finance, Accounting and Budgets
Learn the Excel formulas for finance, accounting and audit work, including loan payments, depreciation, bond duration and ledger checks, with worked examples.
Finance, accounting and audit work keeps returning to the same few calculations: loan payments, depreciation schedules, bond duration and checks that a ledger ties to its source. Building each one by hand is slow, and a single wrong sign or rate period can carry through every total on the sheet. If you need Excel formulas for finance for a budget, a financial model or an audit file, this guide gives you the functions to use and the checks to run on them.
Many beginner lists stop at SUM and AVERAGE. This guide goes further into the functions that accountants, auditors and modelers actually reach for, including depreciation methods and duration measures that generic lists often skip. Each section gives the function syntax, a small example you can reproduce and the mistake that usually produces a wrong answer.
If your source numbers sit in a PDF, such as a lender's repayment table or a bank statement, you can move them into a spreadsheet before you apply formulas. The steps below cover that case as well as work from data you already have.
Which Excel formulas for finance do you need? The short answer
Start with the formulas that match the question in front of you, not the longest list you can find. The table below maps common finance and audit tasks to the functions that handle them.
| Your situation | What to do | Why |
|---|---|---|
| Tracking a budget against actual spending | Use SUMIFS for each category and month, then add a variance column | Shows where spending drifts without rebuilding the sheet |
| Modeling a loan | Use PMT, IPMT, PPMT and CUMIPMT | Returns the payment, the interest and the principal for each period |
| Comparing investment cash flows | Use NPV and IRR, or XNPV and XIRR when cash flows have dates | Puts future cash flows on a common time basis |
| Writing off an asset | Use SLN, SYD, DB or DDB, depending on your policy | Matches the depreciation method your policy requires |
| Measuring a bond's sensitivity to rates | Use DURATION and MDURATION | Returns Macaulay and modified duration from the same inputs |
| Checking a ledger for an audit | Use COUNTIFS to flag duplicates and SUMIFS to tie totals | Gives a repeatable test you can document and rerun |
Before you start
A few preparation steps prevent most of the errors that show up later in finance formulas.
- Pick one sign convention. Decide whether money paid out is negative or positive, and keep it that way. PMT and NPV return results whose signs depend on how you enter the inputs, so mixed conventions produce totals that look plausible but are wrong.
- Match the rate to the period. An annual rate used with monthly payments gives a wrong payment. Divide the annual rate by 12 for monthly periods, and make sure the number of periods is also counted in months.
- Enter dates as real dates. XIRR and XNPV need date values. Dates stored as text cause errors or are ignored, so re-enter them before you run the formula.
- Confirm which depreciation method applies. The formulas calculate whichever method you pick. Your accounting policy or the tax rules for the asset decide which one is correct.
- Make sure a PDF source has readable text. If you are pulling figures from a PDF, check that you can select a line of its text. If you cannot, the file is probably a scan, and you will need OCR first.
Building a loan schedule in Excel, step by step
This example builds a loan schedule from a small test case: a loan of 10,000 repaid over 12 months at the annual rate stated in your own loan documents. Replace the test inputs with your own figures once the formulas return sensible results.
- Get the source numbers into Excel if they are in a PDF. Open the PDF in the OmniDoc desktop client (download it from https://download.omnidoc.top), use the PDF editor to export it to Excel, and confirm each figure landed in the right column. Scanned PDFs need OCR in the same editor before you export.
- Label the inputs in separate cells. Put the loan amount, the annual rate, the number of months and the payment count in their own labeled cells so every formula points to a single source.
- Convert the annual rate to a periodic rate. Enter =annual rate cell/12 in a new cell. Every later formula uses this monthly rate rather than the annual figure.
- Calculate the monthly payment. Enter =PMT(rate cell, months cell, -loan cell). Passing the loan as a negative value makes the payment come back as a positive number, which keeps the sign convention consistent.
- Split the first payment into interest and principal. Enter =-IPMT(rate, 1, months, -loan) for interest and =PPMT(rate, 1, months, -loan) for principal. The two results should add up to the payment from step 4.
- Copy the formulas down the schedule. Change the period number in each row so row 2 uses period 1, row 3 uses period 2, and so on. Lock the input cells with absolute references so they do not shift as you copy.
- Add the interest paid in a year. Enter =-CUMIPMT(rate, months, -loan, 1, 12, 0) to total the interest for periods 1 through 12. The final argument 0 means payments fall at the end of each period.
- Check that the balance reaches zero. The closing balance in the last row should be zero, or within a fraction of a cent of zero because of rounding. If it is not, one of the inputs or the rate conversion is wrong.
Depreciation and duration formulas for accountants and modelers
Depreciation and duration are where generic lists tend to stop short, and where small input errors have the largest effect on reported figures.
Depreciation functions
Excel covers the common straight-line and accelerated methods directly:
- SLN(cost, salvage, life) returns straight-line depreciation for one period, spreading the depreciable amount evenly over the asset's life.
- SYD(cost, salvage, life, period) returns sum-of-years'-digits depreciation for a given period, which front-loads the expense.
- DB(cost, salvage, life, period, [month]) and DDB(cost, salvage, life, period, [factor]) return fixed-declining-balance and double-declining-balance depreciation.
Excel does not include a built-in MACRS function. To model MACRS depreciation, look up the percentage table for the asset's recovery class and multiply the depreciable basis by the rate for each year. Build the table in its own range, reference it with a lookup, and keep the basis cell separate so you can rerun the schedule for a different asset.
Duration functions
For a bond, DURATION(settlement, maturity, coupon, yld, frequency, [basis]) returns Macaulay duration, which is the weighted average time until the bond's cash flows arrive, measured in years. MDURATION takes the same arguments and returns modified duration, which estimates how much the bond's price changes for a small change in yield.
Enter the coupon and yield as decimals in cells, and use real date values for settlement and maturity. Frequency is the number of coupon payments per year, so enter 2 for semiannual coupons and 1 for annual coupons. If you need Macaulay duration only, MDURATION is not required; if you need price sensitivity, use MDURATION.
Budget and ledger checks with SUMIFS and COUNTIFS
Budget variance and ledger checks rely on conditional functions, which work well in audit files because each test can be rerun and documented.
For a budget, put actual spending in one column, the category in another and the month in a third. Then enter =SUMIFS(actual range, category range, category cell, month range, month cell) for each category and month, and subtract the budget figure in an adjacent column to get the variance.
For a ledger check, use COUNTIFS to flag entries that repeat on the same date, amount and reference: =COUNTIFS(date range, date cell, amount range, amount cell, reference range, reference cell). Any result above 1 marks a possible duplicate. Use SUMIFS to compare the total of a ledger account with the balance the source report shows.
Check the result
Before you rely on any formula in a report or model, test it against an independent figure. The table below lists common checks and what to do when one fails.
| Check | How to see it | If it's wrong |
|---|---|---|
| Loan payment | Multiply the payment by the number of periods and compare with the principal plus total interest | Review the rate conversion and confirm the period count is in months |
| Principal and interest | Add the IPMT and PPMT results for one period and compare with the PMT result | Check that the loan sign matches the sign used in PMT |
| Loan balance | Confirm the closing balance in the final period is zero or nearly zero | Look for a rate or period input that does not match the source |
| Depreciation total | Sum the annual charges across the asset's life and compare with cost minus salvage | Confirm the life and salvage inputs and the method you selected |
| Budget variance | Add all category totals and compare with the total of the actual column | Look for text-formatted numbers or categories spelled differently |
| Ledger tie-out | Compare the SUMIFS total with the balance on the source report | Search for duplicates flagged by COUNTIFS and for dates stored as text |
If a source PDF's figures disagree with your sheet, go back to the PDF and confirm the figure you copied, because OCR and manual entry both introduce small errors.
❓ FAQ
1. Why does my PMT result come back negative?
PMT returns the payment with the sign that matches the loan amount you passed in. If you enter the loan as a positive number, the payment comes back negative, so enter the loan as a negative value to get a positive payment.
2. Does Excel have a MACRS function?
No, Excel does not include a built-in MACRS function. Apply the published MACRS percentage table to the asset's depreciable basis, year by year.
3. Can I check a ledger for duplicates without a macro?
Yes. A COUNTIFS formula that tests date, amount and reference together returns any row that repeats across those fields, and you can filter the sheet on results above 1.
4. What if my scanned PDF cannot be exported to Excel?
Run OCR on the scanned PDF in the OmniDoc PDF editor first, then export. If the exported figures look garbled, check the source text by selecting a line in the original PDF and comparing it with the spreadsheet.
📝 Summary
Most finance, accounting and audit calculations come down to a few functions: PMT and its relatives for loans, SLN, DB and SYD for depreciation, DURATION and MDURATION for bonds, and SUMIFS and COUNTIFS for budgets and ledgers. Keep one sign convention, match rates to periods and test every result against an independent figure before you use it.
