Ravindra BagaleCourses & study guides Track your progress
Hard challenge

Hard: Hard-coded NPV

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

NPV shows 49.21 as a value, not a formula. Use =NPV(rate, CF1:CF3)+CF0 so edits recalculate.

About this data: Made-up project for Sana Qureshi (Hyderabad) and Erik Johansson (Stockholm). Rate 10%.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Hard-coded investment metrics become lie-sheets when inputs move.

What: Broken NPV value; fixed NPV formula.

How:

  1. Open Project.
  2. Click NPV — no formula.
  3. Enter NPV formula + t0.
  4. Change a CF — value moves.

Ravindra Bagale’s Tip

Excel NPV discounts from t=1; add t0 outflow outside NPV.

Bugs explained (with mini examples)

Bug 1 — Typed NPV

Dead number.

Bug 2 — Wrong timing

Know t0 vs t1.

Practice tasks (12)

  1. Task 1. Is B11 formula when broken?
    Hint: No.
  2. Task 2. Write NPV formula.
    Hint: =NPV(B10,B6:B8)+B5
  3. Task 3. Rate cell?
    Hint: B10.
  4. Task 4. Change B6 — updates?
    Hint: Yes.
  5. Task 5. Interview.
    Hint: NPV was hard-coded.
  6. Task 6. t0 CF?
    Hint: -1000.
  7. Task 7. Why +B5 outside?
    Hint: t0 convention.
  8. Task 8. Rate 12% scenario.
    Hint: Recalc.
  9. Task 9. Goal Seek needs live formulas.
    Hint: Yes.
  10. Task 10. Protect cell.
    Hint: Optional.
  11. Task 11. Bonus XNPV.
    Hint: Advanced.
  12. Task 12. Document.
    Hint: A12.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Restore NPV formula.
  2. Edits to cashflows recalculate.

Quick quiz

Q1. Typed NPV when inputs change:
Q2. Excel NPV first arg:
Q3. t0 outflow usually:
Q4. Rate in this file:
Q5. Hard-coding metrics is: