Investor Excel Formulas — Beginner (Why + How + Steps)

Investor Excel Formulas — Beginner

This page teaches the core blocks you’ll actually use: NPV/IRR, loans & DSCR, rent schedules, and lookups. For each block you’ll see why it matters, when to use it, and exact steps to build it in Excel.

1) Project Evaluation — NPV & IRR (with dates)

Why investors use it: NPV tells you value today versus the price you pay; IRR tells you the break-even discount rate. You need both for screening and memos.

When to use: any investment with timed cash flows (acquisition, light capex, sale). Prefer XNPV/XIRR when dates are irregular (real life!).

Steps: set up your sheet

Create a table CashFlow with columns Date and CF. Put the purchase price as a negative number on Day 0.
Define a named cell DiscountRate (e.g., 8.5%).
Compute:
- =XNPV(CashFlow[CF], CashFlow[Date], DiscountRate)
- =XIRR(CashFlow[CF], CashFlow[Date])

Periodic alternative (equal spacing)

If your rows are annual and evenly spaced:

=NPV(DiscountRate, C2:G2) + C1    /* C1 = initial outlay (negative) */

=IRR(C1:G2)

Mid-year convention

Approximate “cash during the year” timing:

=SUMPRODUCT(C2:G2, 1/(1+DiscountRate)^((COLUMN(C2:G2)-COLUMN(C2)+1)-0.5)) + C1

Common pitfalls: (1) Forgetting to include the initial outlay, (2) mixing periodic functions with real dates, (3) trusting IRR with multiple sign changes (use MIRR/NPV profile).

2) Loans & DSCR — payment, interest, principal

Why investors use it: most assets are financed; lenders watch DSCR and balloon/refi risk.

When to use: any stabilized or transitional deal with amortization or a refinance.

Steps: build a basic amortization

Inputs (named cells): LoanAmt, RateAnnual, TermMonths.

Monthly payment:

=PMT(RateAnnual/12, TermMonths, -LoanAmt)

For each month t:

=IPMT(RateAnnual/12, t, TermMonths, -LoanAmt)   /* Interest_t */
=PPMT(RateAnnual/12, t, TermMonths, -LoanAmt)   /* Principal_t */

Debt service = Interest + Principal. DSCR by month/year:
```
=NOI_t / DebtService_t
```

Tip: Add a Refi test at maturity: =PMT(NewRate/12, NewTermMonths, -OutstandingAtMaturity) and check DSCR & LTV again.

3) Rent Schedules — dates & escalations

Why investors use it: clean timelines prevent timing errors in cash flow and debt service.

Steps

Create a column of month-end dates from a start date:
```
=EOMONTH(StartDate, SEQUENCE(120,1,0,1))
```
Apply growth annually (for Year n):
```
=Rent_Y1 * (1 + Growth)^(Year-1)
```

Helpful date tools:

=EDATE(Date, months)    =EOMONTH(Date, k)

4) Lookups — join assumptions cleanly

Why investors use it: to pull rates, cap buckets, or tenant terms without hard-coding.

INDEX + MATCH (robust)

=INDEX(Table[Result], MATCH(Key, Table[Key], 0))

Exact VLOOKUP (simple)

=VLOOKUP(Key, Table, ColIndex, FALSE)

Set up in Excel: Turn ranges into Tables (Ctrl+T), name key cells, keep an Inputs sheet with sources & dates.

5) Quick QA & red flags

Highlight DSCR < 1.20: =NOI_t / DebtService_t < 1.2 (conditional format).

Find expiring leases next 12 months:

=FILTER(Leases, (Leases[EndDate] <= EOMONTH(TODAY(),12)) * (Leases[Status]="Active"))

Catch text numbers: =ISNUMBER(cell), =ISTEXT(cell).

Excel Formulas for Investors — Beginner level