# The formula to calculate the Net Present Value of monthly rental payment, adjusted for rental annual increase?

I am renting my apartment out, and for that I need to calculate the Net Present Value of the rental payment, after deducting the management fees (`f`) and adjusted for rental increased.

Assuming that

1. The annual rental increase is `r_a`
2. The annual management fees increase is `r_m`
3. I collect rent for `y` years, say, 10 years
4. The annual inflation is `i`, say, 3% ( `i=3`)

What is the formula that I can use in Google Spreadsheet, to calculate Net Present Value of the rental payment, after deducting the management fees and adjusted for rental increased?

• Why do you need the NPV? I ask because it can make a difference as to whether you use inflation or some other discounting factor. – D Stanley Feb 5 at 15:07
• Because I want to also know when it's the most advantageous time to sell the house, whether 10 years, 20 years or? So need to discount to present in order to have a proper comparison – Graviton Feb 6 at 0:40

Let's say we have the following values

``````annual rent     r = 100
rent increase   a = 0.09
management fee  f = 10
fee increase    m = 0.08
inflation       i = 0.07
``````

At the start of the first year

``````npv = r - f = 100 - 10 = 90
``````

At the start of the second year

``````(r (1 + a) - f (1 + m)) = 98.20
``````

but inflation means the NPV is lower.

``````(r (1 + a) - f (1 + m))/(1 + i) = 91.78
``````

In general

so the formula for NPV is

``````npv = ((i+1)^(1-n) (a f ((i+1)^n-(m+1)^n)-r (i-m) *
((a+1)^n-(i+1)^n)+f i ((m+1)^n-(i+1)^n)))/((i-a) (i-m))
``````

where `n` is the year

``````n = 1    ∴ npv = 90

n = 2    ∴ npv = 181.78     also 91.78 + 90 = 181.78

n = 10   ∴ npv = 984.13
``````

Edit

To handle cases where `i = a` and/or `i = m` the formulas below can be used.

Formatted for Excel

``````=IF(AND(i<>a,i<>m),
((i+1)^(1-n)*(a*f*((i+1)^n-(m+1)^n)-r_*(i-m)*((a+1)^n-(i+1)^n)+
f*i*((m+1)^n-(i+1)^n)))/((i-a)*(i-m)),
IF(AND(i<>a,i=m),(-a*f*n+(m+1)*r_*(((a+1)/(m+1))^n-1)+f*m*n)/(a-m),
IF(AND(i=a,i<>m),((a+1)*f*(((m+1)/(a+1))^n-1))/(a-m)+n*r_,
n*(r_-f))))
``````

• In Google spreadsheet there is a function 'npv', how should I plug in each term? – Graviton Feb 6 at 0:41
• I'm not too sure whether your npv formula is correct-- what if `i==a` or `i==m`? then the NPV is infinity? – Graviton Feb 6 at 3:06
• @Graviton Some terms cancel if `i==a` or `i==m`. A simpler formula to read is `npv = (r(a+1)^n(i+1)^(1-n)-(i+1))/(a-i) - (f(m+1)^n(i+1)^(1-n)-(i+1))/(m-i)` where the first term collapses to just `r` if `i==a`, and the second term collapses to just `f` if `i==m`. – kccu Feb 6 at 3:43
• @kccu, is there a built-in spreadsheet function that handles this? I tried implementing the formula in spreadsheet and it got very messy with the `if` condition and all of the terms – Graviton Feb 6 at 3:47
• @Graviton You can try to input the series in Chris's answer. I don't know much about inputting finite series into a spreadsheet (at least when the upper limit of the sum is variable), but it can be done in excel: stackoverflow.com/questions/38044955/… – kccu Feb 6 at 4:03