3

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?

2
  • 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, 2019 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, 2019 at 0:40

1 Answer 1

1

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

enter image description here

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))))

enter image description here

8
  • In Google spreadsheet there is a function 'npv', how should I plug in each term?
    – Graviton
    Feb 6, 2019 at 0:41
  • 1
    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, 2019 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, 2019 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, 2019 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, 2019 at 4:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .