Excel for Finance Intermediate: Loans, Appraisal, Depreciation, GST and Cash Flow

For accountants, finance executives and business owners who already know SUMIFS and basic finance functions. Eight lessons on the jobs that fill a finance week: loan schedules and prepayments, NPV/IRR/XIRR, depreciation under the Companies Act and income-tax rules, GST reconciliation against GSTR-2B, TDS on payments, budget vs actual, a 13-week cash forecast and a ratio dashboard.

Every lesson has a workbook with automatic checks, and every expected answer was calculated by Excel itself.

Before you start: the Excel for Finance Course and comfort with XLOOKUP and dynamic arrays (Excel Intermediate).

1. 1Excel for Finance Intermediate Lesson 1: Loan Schedules and the Effect of PrepaymentEMI, interest and principal split, year-1 interest for tax, outstanding balance and how many EMIs a ₹2 lakh prepayment saves, with PMT,…
2. 2Excel for Finance Intermediate Lesson 2: NPV, IRR, XIRR and PaybackCompare two projects with NPV and IRR, find the payback year, and measure a real SIP return with XIRR using actual dates.
3. 3Excel for Finance Intermediate Lesson 3: Depreciation – SLM, WDV and the 180-Day RuleBook depreciation under the Companies Act (SLM and WDV from useful life), time-proportionate depreciation, and income-tax block rates with the half rate…
4. 4Excel for Finance Intermediate Lesson 4: GST Reconciliation – Books vs GSTR-2BMatch purchase invoices in your books with GSTR-2B by GSTIN and invoice number: find missing invoices, extra ones and value differences, and…
5. 5Excel for Finance Intermediate Lesson 5: TDS on Payments – Rates, Limits and No-PAN CasesCalculate TDS on contractor, professional and rent payments with a rate table and XLOOKUP, apply single-payment limits and the higher rate when…
6. 6Excel for Finance Intermediate Lesson 6: Budget vs Actual Variance ReportingVariance in rupees and %, Over/Under/OK status with tolerance bands, the biggest overspend and conditional formatting that makes problems obvious.
7. 7Excel for Finance Intermediate Lesson 7: A 13-Week Cash Flow ForecastBuild a rolling 13-week cash forecast: running balance with SCAN, the first week below the minimum buffer, how much funding is needed…
8. 8Excel for Finance Intermediate Lesson 8: Project – A Financial Ratio DashboardFinal project: margins, current and quick ratio, debtor days, debt-to-equity, interest cover and growth for two years, with a one-page dashboard.

Automate the data side with the Bank Statement Automation course or Power Query Intermediate.