Day 26: Financial Analysis in Excel

NPV(rate, value1, [value2],...) calculates the Net Present Value of future cash flows at a given discount rate. A positive NPV means the investment generates value above the discount rate — accept it. A negative NPV means the investment destroys value — reject it. Important Excel quirk: Excel's NPV function assumes the first cash flow occurs at the END of period 1, not immediately. If there is an initial investment at time 0, exclude it from NPV and subtract it separately: =NPV(rate, future_cashflows) - initial_investment.

IRR(values) calculates the Internal Rate of Return — the discount rate at which NPV equals zero. If the IRR is greater than the company's cost of capital (or hurdle rate), the investment is worth making. Excel's IRR assumes cash flows occur at regular intervals. For irregular intervals, use XIRR(values, dates). XIRR is almost always more accurate for real cash flows that do not occur at perfectly regular intervals.

PMT(rate, nper, pv) calculates the periodic payment for a loan or annuity. rate = interest rate per period, nper = total number of payments, pv = present value (loan amount, as negative). Example: monthly payment on a ₹50 lakh home loan at 8.5% annual interest over 20 years: =PMT(8.5%/12, 20*12, -5000000). Returns the monthly EMI. PPMT returns the principal portion of a specific payment; IPMT returns the interest portion — together they build an amortisation schedule.

YoY growth: =(CurrentYear - PriorYear) / PriorYear. Format as percentage. For a table with monthly data across years, use INDEX-MATCH or VLOOKUP to pair the same month in different years. MoM growth: =(CurrentMonth - PriorMonth) / PriorMonth. In a Pivot Table, use "Show Values As" → "Difference From" → "Previous" to calculate these automatically for the entire table without formulas.

Structure: Revenue at top, then Cost of Goods Sold, then Gross Profit (= Revenue - COGS). Then Operating Expenses itemised below (Salaries, Rent, Marketing, etc.), then Total OpEx, then Operating Profit (EBIT = Gross Profit - Total OpEx). Then non-operating items (Interest expense), then Profit Before Tax, Tax (at applicable rate), then Net Profit. Each line references a single source cell or summary formula — never hard-code values in a P&L. Use named ranges for tax rates and other parameters.