Google Sheets Formulas for Personal Finance

=income * 0.50 (needs), =income * 0.30 (wants), =income * 0.20 (savings) Budgeting

Calculate how much to allocate to needs, wants, and savings based on the popular 50/30/20 budgeting rule.

Amortization Schedule

=PMT(rate/12, periods, -principal) Debt & Loans

Build a complete loan payment schedule showing principal, interest, and remaining balance for each payment.

Annual to Hourly Salary Converter

=annual_salary / 2080 Income

Convert between annual salary, monthly, weekly, and hourly pay rates.

Array Formulas

=ARRAYFORMULA(formula_applied_to_range) Utilities

Perform calculations across entire ranges at once, enabling powerful automated financial analysis.

Budget Variance

=planned_amount - actual_amount Budgeting

Calculate the difference between planned budget and actual spending to see if you're over or under budget.

CAGR (Annualized Return)

=(ending_value/beginning_value)^(1/years)-1 Investment

Calculate the compound annual growth rate - the smoothed annual return of an investment over time.

Car Affordability Calculator

=monthly_income * 0.10 Affordability

Calculate how much car you can afford based on income using the 10-15% rule.

Coast FIRE Calculator

=retirement_target / (1 + return_rate)^years_until_retirement Savings & Interest

Calculate when you can stop saving for retirement and let compound growth do the rest.

College Savings Calculator (529 Plan)

=PMT(return_rate/12, years*12, 0, -future_college_cost) Education

Calculate how much to save monthly for your child's college education using a 529 plan.

Compound Interest (FV)

=FV(rate/12, periods, -monthly_contribution, -initial_deposit) Savings & Interest

Calculate how much your savings will grow over time with compound interest.

Conditional Formatting Formulas

=A2>B2 (to highlight when actual exceeds budget) Utilities

Formulas to use with conditional formatting for visual alerts in your budget spreadsheet.

Credit Card Minimum Payment

=MAX(balance * 0.01 + monthly_interest, 25) Debt & Loans

Calculate how minimum payments are determined and why paying only minimums costs so much.

Data Validation Dropdown

Data > Data validation > Dropdown Utilities

Create dropdown menus for consistent category selection in budget tracking and expense logging.

Days Until Date

=target_date - TODAY() Utilities

Calculate the number of days remaining until a target date for financial deadlines and goals.

Debt Payoff Timeline

=NPER(rate/12, -payment, balance) Debt & Loans

Calculate how many months it will take to pay off a debt with fixed monthly payments.

Debt-to-Income Ratio

=total_monthly_debt / gross_monthly_income Debt & Loans

Calculate your DTI ratio - a key metric lenders use and an important indicator of financial health.

Dividend Yield

=annual_dividend / current_price Investment

Calculate the annual percentage return from dividends relative to stock price.

Dollar Cost Averaging

=total_invested / total_shares Investment

Calculate your average cost per share when investing a fixed amount regularly.

Emergency Fund Target

=monthly_expenses * months_coverage Savings & Interest

Calculate your ideal emergency fund size based on your monthly expenses and risk factors.

Extra Payment Impact

=NPER(rate/12, -(payment + extra), balance) - NPER(rate/12, -payment, balance) Debt & Loans

Calculate how much time and interest you save by making extra payments on your debt.

FIRE Number Calculator

=annual_expenses * 25 FIRE

Calculate your Financial Independence Retire Early target based on annual expenses.

How Much Do I Need to Retire

=annual_expenses * 25 Savings & Interest

Calculate your retirement savings target based on your expected expenses and the 25x rule.

Inflation-Adjusted Value

=future_value / (1 + inflation_rate)^years Savings & Interest

Calculate the real purchasing power of future money by accounting for inflation.

Internal Rate of Return (IRR)

=IRR(cash_flows) Investment

Calculate the annualized return rate of an investment with multiple cash flows over time.

Monthly Average Spending

=AVERAGEIF(category_range, "Category", amount_range) Budgeting

Calculate your average spending per category over multiple months to spot trends and set realistic budgets.

Monthly Loan Payment (PMT)

=PMT(rate/12, periods, -principal) Debt & Loans

Calculate the monthly payment required to pay off a loan with a fixed interest rate over a set number of periods.

Mortgage Affordability Calculator

=monthly_income * 0.28 Affordability

Calculate how much house you can afford based on income, debts, and the 28/36 rule.

Net Present Value (NPV)

=NPV(discount_rate, cash_flows) + initial_investment Investment

Calculate the current dollar value of future cash flows, discounted at your required rate of return.

Net Worth Growth Rate

=(current_net_worth - previous_net_worth) / previous_net_worth Investment

Calculate how fast your net worth is growing year-over-year to track financial progress.

Percentage of Budget Used

=spent/budget Budgeting

Calculate what percentage of your budget you've spent to track progress throughout the month.

Portfolio Allocation Percentage

=investment_value / total_portfolio_value * 100 Investment

Calculate what percentage of your total portfolio each investment represents.

QUERY for Financial Data

=QUERY(data, "SELECT columns WHERE conditions") Utilities

Use SQL-like queries to filter, sort, aggregate, and analyze transaction data without complex formulas.

Rent vs Buy Calculator

=buy_costs - rent_costs Affordability

Compare the true cost of renting versus buying a home over time.

Required Monthly Savings

=PMT(rate/12, months, -current_savings, goal) Savings & Interest

Calculate how much you need to save each month to reach a financial goal by a target date.

Return on Investment (ROI)

=(current_value - initial_investment) / initial_investment * 100 Investment

Calculate the percentage return on an investment relative to its cost.

Rule of 72

=72/annual_rate Savings & Interest

Quickly estimate how long it takes for an investment to double at a given interest rate.

Running Balance

=starting_balance + SUM($C$2:C2) Budgeting

Track your account balance after each transaction with a cumulative running total.

Safe Withdrawal Rate (4% Rule)

=portfolio_value * 0.04 Savings & Interest

Calculate how much you can withdraw annually from your retirement portfolio without running out of money.

Savings Goal Timeline

=NPER(rate/12, -monthly_savings, current_savings, -goal_amount) Savings & Interest

Calculate how many months it will take to reach a savings goal with regular contributions.

Sparkline Trends

=SPARKLINE(data_range, {"charttype","line"}) Utilities

Create mini charts inside cells to visualize spending trends, account balances, and financial progress.

Sum Expenses by Category

=SUMIF(category_range, "Category Name", amount_range) Budgeting

Add up all transactions that match a specific category, like "Groceries" or "Entertainment".

SUMIFS with Multiple Criteria

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) Utilities

Sum values that match multiple conditions, like totaling expenses for a specific category within a date range.

Take-Home Pay Calculator

=gross_pay - federal_tax - state_tax - fica_tax - deductions Income

Estimate your net pay after federal, state, and FICA taxes are deducted from gross income.

Total Interest Paid

=(PMT(rate/12, periods, -principal) * periods) - principal Debt & Loans

Calculate the total amount of interest you'll pay over the life of a loan.

VLOOKUP for Category Matching

=VLOOKUP(lookup_value, table_range, column_index, FALSE) Utilities

Automatically look up and return information based on a category or transaction name.

XIRR (Irregular Cash Flows)

=XIRR(cash_flows, dates) Investment

Calculate investment returns when cash flows occur at irregular intervals, not fixed periods.

Years to Financial Independence