Finance basics
Loans, interest, depreciation, amortization, tax, margin, ROI, CAGR, break-even — every classic finance formula as a verified recipe.
38 pages · 38 how-to
How-to guides
How to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.
How to calculate compound interest
Compute a compounded future value with principal × (1 + rate)^years using POWER. Identical in Excel and Google Sheets.
How to calculate cost per unit
Divide total cost by total units to find cost per unit using a simple formula in Excel or Google Sheets.
How to calculate a selling price from a target margin
Use cost and target gross margin percentage to back-calculate the required selling price in Excel or Google Sheets.
How to calculate simple interest
Multiply principal, rate, and time to compute simple interest using a straightforward formula in Excel or Sheets.
How to calculate a loan payment
Use the PMT function to calculate periodic loan payments given a principal, interest rate, and number of periods.
How to calculate a monthly loan payment
Compute the exact monthly payment for any loan using the PMT function with monthly rate and total payment count.
How to calculate future value
Use the FV function to project the future value of an investment given a rate, number of periods, and optional payments.
How to calculate present value
Use the PV function to find today's value of a future cash flow or series of payments discounted at a given rate.
How to calculate markup
Divide the difference between selling price and cost by cost to calculate markup percentage in Excel or Google Sheets.
How to calculate profit margin
Divide net profit by revenue to calculate gross or net profit margin percentage in Excel and Google Sheets.
How to calculate sales tax
Multiply the pre-tax price by the tax rate to compute the sales tax amount, or by (1 + rate) to get the total price.
How to calculate VAT
Multiply the net price by the VAT rate to find the VAT amount, or use (1 + rate) to compute the VAT-inclusive total.
How to calculate a discount
Multiply the original price by the discount rate to find the discount amount, then subtract to get the discounted price.
How to calculate commission
Multiply sales revenue by the commission rate to compute the commission earned in Excel and Google Sheets.
How to calculate ROI
Calculate Return on Investment by dividing net gain by the initial cost, expressed as a percentage in Excel or Sheets.
How to calculate the break-even point
Divide fixed costs by the contribution margin (price minus variable cost) to find the break-even quantity.
How to calculate CAGR
Compute Compound Annual Growth Rate by raising the ratio of end value to start value to the power of (1/years) minus 1.
How to convert currency
Multiply an amount by an exchange rate to convert between currencies; use a rate table and VLOOKUP for multi-currency conversions.
How to calculate an hourly rate
Divide annual salary by total working hours per year to compute an effective hourly rate in Excel or Google Sheets.
How to calculate depreciation
Use the SLN function for straight-line depreciation or DB for declining balance; both work in Excel and Google Sheets.
How to calculate net pay
Calculate net pay by subtracting total deductions from gross pay using a simple formula in Excel or Google Sheets.
How to calculate overtime pay
Calculate regular and overtime pay by applying a higher rate to hours exceeding a threshold using IF and MAX in Excel and Google Sheets.
How to prorate an amount
Prorate a monthly fee, salary, or charge based on the number of days active in a period using simple division and multiplication.
How to calculate a running balance
Build a running balance column in a ledger by referencing the previous balance and adding or subtracting the current transaction amount.
How to split a bill evenly
Divide a total bill evenly among a group and optionally add a tip, calculating each person's share with a simple formula.
How to convert an annual salary to monthly
Divide an annual salary by 12 to get the monthly gross amount, with options for semi-monthly and bi-weekly pay periods.
How to calculate GST
Calculate GST (Goods and Services Tax) on a pre-tax amount or extract GST from a GST-inclusive total in Excel and Google Sheets.
How to calculate income tax with slabs
Calculate progressive income tax using nested IF statements or a lookup table to apply different rates across salary brackets in Excel and Google Sheets.
How to calculate take-home pay
Calculate take-home (net) pay by subtracting income tax, social security, and other deductions from gross pay in Excel and Google Sheets.
How to calculate a remaining loan balance
Use PV or the CUMPRINC function to find the remaining principal balance on a loan after a given number of payments in Excel and Google Sheets.
How to work out the monthly savings to reach a goal
Use PMT to calculate the monthly savings amount needed to reach a future financial goal given a timeline and interest rate.
How to calculate a tip and total
Calculate a tip amount and grand total from a subtotal and tip percentage using straightforward multiplication in Excel and Google Sheets.
How to build a loan amortization schedule
Build a full loan amortization schedule row by row using PMT, IPMT, and PPMT in Excel and Google Sheets, with running balance and cumulative interest columns.
How to build a depreciation schedule
Build a straight-line or declining-balance depreciation schedule using SLN and DDB in Excel and Google Sheets, with annual book value tracking.
How to build a fixed payment schedule
Build a full fixed-payment amortization schedule in Excel or Google Sheets using PMT, IPMT, and PPMT to break each period into interest and principal.
How to calculate the effective annual interest rate
Calculate the effective annual rate (EAR) in Excel or Google Sheets using the EFFECT function to compare interest rates with different compounding frequencies on a fair basis.