Excel Financial Functions: A Guide to Loan, Investment, and Depreciation Calculations

Whether you’re planning to take out a loan, evaluate a business investment, or track how your company’s equipment loses value over time, Excel has a set of built-in financial functions that handle these calculations instantly. Instead of working through complex financial formulas by hand, functions like PMT, PV, NPV, FV, IRR, SLN, and DB let you enter a few key values and get precise answers in seconds. This guide walks through each of these functions – what they calculate, how their syntax works, and where you’d realistically use them.

Understanding financial functions in Excel 🔗

Excel’s financial functions are grouped into practical categories: loan calculations, time value of money, cash flow analysis, depreciation, and securities valuation. The ones most relevant to everyday business and personal finance decisions are PMT, PV, NPV, FV, IRR, SLN, and DB. Before diving into each one, it helps to understand one key convention: Excel treats money flowing out (payments you make) as negative numbers and money coming in (returns you receive) as positive numbers. Getting this sign convention right is critical – a wrong sign will flip your result entirely.

PMT – calculating loan payments 🔗

The PMT (Payment) function tells you the fixed periodic payment required to repay a loan in full. Its syntax is:

=PMT(rate, nper, pv, [fv], [type])

Where rate is the interest rate per period, nper is the total number of payment periods, and pv is the present value – meaning the loan amount. A practical example: you’re taking out a $300,000 mortgage at a 6% annual interest rate for 30 years. The formula would be =PMT(6%/12, 30*12, 300000). Notice that the annual rate is divided by 12 to get a monthly rate, and the years are multiplied by 12 to get total months. Excel will return -$1,798.65, indicating that amount leaves your account each month. The negative sign simply confirms it’s a cash outflow.

PMT is commonly applied in loan analysis, leasing decisions, and personal finance budgeting. It is also useful for reverse-engineering how much you can afford to borrow, given a monthly payment you’re comfortable with.

PV – present value of an investment or loan 🔗

The PV (Present Value) function answers the question: what is a series of future payments worth in today’s money? Its syntax is:

=PV(rate, nper, pmt, [fv], [type])

The arguments mirror PMT. For instance, if you want to know the present value of receiving $200 per month for 5 years at an annual interest rate of 5%, you’d write =PV(5%/12, 60, 200). This is useful when comparing two investment options that pay out at different times – PV converts everything to a common baseline so you can make an apples-to-apples comparison. It is also widely used in loan amortization schedules and bond valuation.

One practical application: a parent wants to save enough today so that a lump sum grows to $50,000 in 18 years at 8% annual interest. Using =PV(8%, 18, 0, 50000), Excel shows that just over $12,500 invested today would reach that target – without any additional contributions.

NPV – net present value of an investment 🔗

The NPV (Net Present Value) function calculates the present value of a series of future cash flows, discounted at a specific rate. Its syntax is:

=NPV(rate, value1, [value2], …)

There’s an important nuance here that trips up many users: Excel’s NPV function does not actually compute net present value in the traditional sense – it calculates the present value of the cash flow series only. To get the true NPV, you must subtract the initial investment separately. For example, if a project costs $800 upfront and generates returns over five years, the correct formula is =NPV(rate, B5:B9) + B4, where B4 contains the negative initial outlay.

NPV is used for capital budgeting decisions. If NPV is positive, the investment is expected to generate more value than it costs. A negative NPV means the opposite. Businesses regularly use it when evaluating whether to launch a new product, open a new branch, or acquire equipment.

Calculating depreciation with SLN and DB 🔗

Every physical asset – machinery, vehicles, office equipment – loses value over time. Depreciation is how businesses account for that loss in their financial records. Excel offers several depreciation functions, but the two most commonly used are SLN (straight-line) and DB (declining balance). They represent fundamentally different philosophies about how an asset loses value.

SLN – straight-line depreciation 🔗

The SLN function is the simplest depreciation method. It spreads the loss of value evenly across every year of an asset’s useful life. The syntax is:

=SLN(cost, salvage, life)

Where cost is the original purchase price, salvage is the estimated value at the end of the asset’s life, and life is the number of periods over which it is depreciated. According to Microsoft’s documentation, SLN returns the same depreciation value for each period. Using the formula from Corporate Finance Institute: if an asset costs $50,000, has a salvage value of $5,000, and a useful life of 5 years, =SLN(50000, 5000, 5) returns $9,000 – meaning $9,000 is written off as depreciation every year, no more, no less.

SLN works well for assets that provide consistent utility year after year, like office furniture or buildings. Its predictability makes budgeting straightforward.

DB – declining balance depreciation 🔗

The DB function uses the fixed-declining balance method, which front-loads depreciation – higher amounts in early years, progressively less as the asset ages. The syntax is:

=DB(cost, salvage, life, period, [month])

The added argument period specifies the year for which you want to calculate depreciation. For example, using a $10,000 asset with a $1,000 salvage value over 10 years, Excel applies a fixed rate of approximately 20.6% each year, resulting in depreciation of $2,060 in year one, $1,635 in year two, and so on – declining with each period.

The month argument is optional and particularly useful when an asset is purchased mid-year. For instance, setting it to 9 tells Excel the asset was bought with 9 months remaining in the first year, adjusting the calculation accordingly.

DB suits assets that lose value rapidly early on – technology equipment or vehicles are good examples. A new car depreciates significantly the moment it leaves the lot; the declining balance method reflects that reality more accurately than SLN.

SLN vs. DB – which should you use? 🔗

The core difference comes down to the pattern of depreciation. SLN produces a flat, constant depreciation charge each year. DB calculates depreciation at a fixed rate applied to the remaining book value, so the charge is highest in year one and falls progressively. The total depreciation over the asset’s full life is the same either way – only the timing differs. The right choice depends on the nature of the asset and your accounting or tax requirements. Many businesses use SLN for simplicity in financial reporting and DB (or its variant DDB) for tax purposes where accelerated depreciation is permitted.

Future value (FV) and internal rate of return (IRR) 🔗

While PMT and PV focus on the present cost of money, FV and IRR look forward – helping you understand what an investment will grow into and whether it delivers an acceptable return.

FV – future value of an investment 🔗

The FV (Future Value) function calculates what a series of regular contributions or a lump-sum investment will be worth at a future point, assuming a constant interest rate. The syntax is:

=FV(rate, nper, pmt, [pv], [type])

FV is essentially the mirror image of PV. Where PV tells you what future money is worth today, FV tells you what today’s money or regular contributions will grow into. Consider a retirement planning example: if you invest $1,000 per month for 5 years at a 5% annual interest rate, the formula is =FV(5%/12, 60, -1000), and Excel returns $68,006.08 – the total value of those contributions plus compounded interest at the end of the period.

FV is especially valuable for retirement planning, savings goal projections, and evaluating the long-term benefit of consistent investment contributions. It makes the effect of compounding interest visible in concrete dollar terms.

IRR – internal rate of return 🔗

The IRR (Internal Rate of Return) function calculates the discount rate at which the net present value of a set of cash flows equals zero – in other words, the effective annual return rate of an investment. The syntax is:

=IRR(values, [guess])

The values argument is a range of cash flows that must include at least one negative value (the initial investment) and one positive value (the return). The optional guess argument gives Excel a starting estimate – if omitted, it defaults to 10%. As an example from Finally Learn: if a project requires a $5,000 investment and generates returns of $1,500, $2,000, $2,500, and $3,000 over four years, the IRR formula applied to that range returns approximately 25.27% – meaning that project effectively grows money at that annual rate.

IRR is widely used to compare investment alternatives. If an investment’s IRR exceeds the required rate of return (often called the hurdle rate or cost of capital), the investment is typically considered worthwhile. However, IRR should always be read alongside NPV, not in isolation. A higher IRR does not always mean a better investment – it may reflect scale differences, unusual cash flow timing, or higher risk.

When to use NPV vs. IRR 🔗

NPV and IRR answer related but different questions. NPV tells you the absolute value an investment creates (in dollars), while IRR tells you the percentage return. Both should be used together when making investment decisions. A project with a positive NPV and an IRR above the cost of capital is generally a strong candidate. If the two metrics point in different directions – for example, if Project A has a higher IRR but lower NPV than Project B – the project with the higher NPV typically creates more total value for the business.

Quick reference: syntax summary 🔗

Here’s a concise overview of all the functions covered in this guide:

PMT(rate, nper, pv) – periodic payment for a loan. PV(rate, nper, pmt) – present value of future payments. NPV(rate, value1, value2…) – present value of uneven cash flows (subtract initial cost separately for true NPV). FV(rate, nper, pmt) – future value of an investment. IRR(values) – effective annual return rate of a set of cash flows. SLN(cost, salvage, life) – equal annual depreciation. DB(cost, salvage, life, period) – front-loaded annual depreciation using a fixed rate.

A few practical tips that apply across all of these functions: always match the interest rate to the payment period (divide annual rates by 12 for monthly calculations, by 4 for quarterly). Keep the sign convention consistent – outflows are negative, inflows are positive. And whenever the output looks unexpected, check timing and sign conventions first, as these are the most common sources of error.

What do you think? If you were choosing between SLN and DB depreciation for a fleet of company vehicles, which method would better reflect the actual pattern of value loss – and why? And when evaluating two investment projects with different NPV and IRR outcomes, how would you decide which metric to prioritize?

References

1. https://macabacus.com/blog/excels-top-financial-functions-for-powerful-modeling
2. https://www.getelyxai.com/en/blog/excel-financial-formulas
3. https://biz.libretexts.org/Courses/Aurora_University/Principles_of_Financial_Management/17:_Excel_and_Data_Analytics_for_Finance/17.03:_Excel_Functions_for_Finance_(NPV_IRR_PMT_Rate_etc.)
4. https://www.travishale.com/exploring-excels-powerful-financial-functions-pv-fv-npv-irr-and-more/
5. http://www.tvmcalcs.com/calculators/excel_tvm_functions/excel_tvm_functions_page3
6. https://support.microsoft.com/en-us/office/sln-function-cdb666e5-c1c6-40a7-806a-e695edc2f1c8
7. https://corporatefinanceinstitute.com/resources/excel/straight-line-sln-function-depreciation/
8. https://www.excel-easy.com/examples/depreciation.html
9. https://excelforengineers.com/dealing-with-depreciation-sln-syd-db-ddb-and-vdb/
10. https://www.slideshare.net/slideshow/financial-functions-in-excel/71713616
11. https://finallylearn.com/financial-functions-in-excel/