Overview
Deal Manager uses the Excel PMT function to calculate NER based on discount rate, term, and total NPV of the deal. This article explains the formula behind the PMT function, how NPV is derived, and how to replicate the calculation manually.
Key benefits / use cases
Understand how NER is computed in Deal Manager.
Replicate NER calculations outside Excel for validation.
Gain clarity on the relationship between PMT and NPV in rent calculations.
How NER is calculated
NER in Deal Manager uses the following formula:
NER = (-1) * PMT(Discount Rate / 12, Term in months, Total NPV / SF of the deal, 0, 1) * 12
This can be broken down into its mathematical equivalent:
Rate = Discount Rate / 12 Nper = Term in months Pv = Total NPV of the deal PMT = (Pv * Rate * (1 + Rate)^Nper) / [(1 + Rate)^Nper − 1]
How NPV is calculated
Total NPV is derived using Excel’s NPV function:
NPV(Discount Rate / 12, All Monthly Net Cash Flow / SF values) – (Initial Costs at month 0)
Mathematically, this is the summation of:
(Net Cash Flow / SF for each month) / (1 + Discount Rate / 12)
Final NER formula
Based on the PMT calculation, NER can be expressed as:
NER = (-1) * (12) * [Total NPV of the deal * (Discount Rate / 12) * (1 + (Discount Rate / 12))^(Month term of deal)] / [(1 + (Discount Rate / 12))^(Month term of deal) − 1]
Example calculation
Given:
Discount Rate: 9% (0.09)
Term of Deal: 12 months
Rent: $22
Net Cash Flow per month per SF: $22 ÷ 12 = $1.83/SF
Initial cost: $100,000
SF of deal: 5,000
Initial cost per SF: $20
Step 1: Calculate NPV
NPV = NPV(0.09 / 12, 1.83 repeated for 12 months) + (-20) NPV ≈ 0.93
Deal Manager calculates slightly higher (≈ 0.96) because it treats the range of cash flows as a single summation rather than individual values.
Step 2: Calculate NER
NER = (-1) * PMT(0.09 / 12, 12, 0.96, 0, 1) * 12 NER ≈ 0.9999 or $1.00
FAQs
What does PMT represent in this context?
PMT calculates the payment amount for a loan or investment based on constant interest rate and term. In Deal Manager, it’s adapted to compute NER using NPV as the present value.
Why does Deal Manager’s NPV differ slightly from Excel?
Deal Manager uses a more accurate approach by summing all monthly cash flows as a single value rather than treating each individually.
Can I replicate this calculation without Excel?
Yes. Use the mathematical formula provided for PMT and NPV to compute NER manually.