2

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

  • In Google spreadsheet there is a function 'npv', how should I plug in each term? – Graviton Feb 6 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 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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.