Theory
Should Meera take the loan?
Meera wants to open a new shop and needs a 5 lakh loan. Two questions keep her up at night: what will the monthly EMI be, and is the new shop even worth the investment?
Both are money decisions, and both have a one-formula answer in Excel. PMT computes the EMI; NPV judges the investment.
These are not just exam functions, they are the exact maths behind every home loan, car loan and business plan. A BCA graduate who can build a loan calculator or an investment appraisal in Excel is immediately useful. Let us build both.
Theory
Money today beats money later
Offered 1000 rupees now or 1000 rupees next year, everyone takes it now, you could invest it, and inflation eats the later amount. This is the time value of money: a rupee today is worth more than a rupee tomorrow. NPV is the tool that fairly compares money arriving at different times by pulling all future amounts back to today's value. It is how you judge whether future profits justify spending now.
Theory
PMT: the EMI calculator
PMT(rate, nper, pv) returns the fixed periodic payment:
- rate = interest per period. For a monthly EMI on a 12% annual loan, use
12%/12= 1% per month. - nper = number of periods. A 5-year loan paid monthly =
5*12= 60. - pv = the loan amount (present value), 500000.
=PMT(12%/12, 60, 500000) gives the monthly EMI (as a negative number, it is money leaving your pocket, so negate it for display).
The golden rule: match the units, monthly rate with monthly periods. Mixing an annual rate with monthly periods is the classic error.
Quiz
For a monthly EMI on a loan at 12% per year over 5 years, what rate and nper does Aryan use in PMT?
- rate = 12%/12 (1% monthly), nper = 5*12 (60 months)
- rate = 12%, nper = 5
- rate = 12%, nper = 60
- rate = 12%/12, nper = 5
Show the answer
rate = 12%/12 (1% monthly), nper = 5*12 (60 months)
For monthly payments, both the rate and the period count must be monthly: rate = annual/12 = 1% per month, and nper = years x 12 = 60 months. Using the annual 12% with 60 monthly periods (option C) massively overstates interest. Unit-matching (rate per period with number of periods) is the single most tested, most error-prone part of PMT.
Think first
Is the new shop worth it?
Meera invests 5 lakh now and the new shop is expected to return 1.5 lakh profit each year for 5 years. Aryan uses NPV. In plain terms, how does NPV help decide, and what result means 'go ahead'?
Show the answer
NPV discounts each year's future profit back to today's value (at a chosen rate, say the loan's interest), then compares the total to the 5 lakh spent now. If the NPV of the returns exceeds the cost (a positive net NPV), the future profits are worth more than the upfront spend, go ahead. If negative, the money would do better elsewhere. NPV turns 'is 1.5 lakh a year for 5 years worth 5 lakh today?' into a single comparable number, accounting for the time value of money.
Watch out
Where marks leak
The big PMT error: mismatching units, using an annual rate with monthly periods (divide the rate by 12 AND multiply years by 12). Forgetting PMT returns a negative (outflow). For NPV: not understanding it applies the time value of money (future cash is worth less today), and that a positive net NPV signals a worthwhile investment. State the time-value concept, examiners want the reasoning, not just the syntax.
Theory
Build a loan calculator this week
A live EMI calculator (change the rate or term, watch the EMI update) is a fantastic practice project and a portfolio piece, it shows you can turn a real decision into a spreadsheet tool. Next lesson moves to data control: sorting, filtering, and data validation (drop-down lists that stop bad data at entry), tools that keep Meera's growing sheets clean and consistent.
Summary
Key takeaways
- PMT(rate, nper, pv) computes the fixed EMI on a loan.
- Match units: for monthly EMIs use rate = annual/12 and nper = years x 12; the result is negative (an outflow).
- NPV(rate, cashflows) discounts future cash to today's value using the time value of money.
- A positive net NPV (returns worth more than the upfront cost) means the investment is worthwhile.
- Unit-mismatching in PMT is the number-one error; state the time-value concept for NPV.
- Memory hook: a rupee today beats a rupee tomorrow, NPV compares fairly across time.