Discounting Problems

1
Discounting Problems

• Andi Wijayanto, S.Sos, M.Si

2
Simple Discounting Problems

• Example 1
• What is the present value of the right to receive
  25,000 in five years, discounting at 6.5 per
  annum?
• Function required
• PV(rate, nper, pmt, fv, type)
• PV(6.5,5,0,25000,0)
• 18,247.02
• The following cross-check formula does indeed
  return 25,000
• FV(6.5,5,0,-18247.02,0)

3
Simple Discounting Problems

• EXAMPLE 2
• A property yields a rental of 25,000 for the
  next 25 years. If I discount at 8, how much
  should I pay? Assume a zero value after 25 years
  and that rent is paid annually in arrears.
• Function required PV(rate, nper, pmt, fv, type)
• The following formula returns 266,869.40
• PV(8,25,25000,0,0)
• This result can be checked using the RATE
  function. This formula returns 8.00
• RATE(25,25000,-266869.40,0,0)

4
Simple Discounting Problems

• EXAMPLE 3
• A property currently worth 2,000,000 is subject
  to a lease at a peppercorn rent for five years. A
  purchaser has paid 1,750,000 for it. Assuming no
  future growth in value, what was the discount
  rate?
• Function required RATE(nper, pmt, pv, fv, type,
  guess)
• RATE(5,0,-1750000,2000000,0)
• 2.706609
• To check the answer, use this formula
• FV(2.706609,5,-1750000,0)

5
Simple Discounting Problems

• EXAMPLE 4
• A leasehold interest in a property was recently
  sold for 230,000. The lease had four years to
  run, and rent was payable at 6,000 per month in
  advance without rent review or escalation. If we
  accept a yield of 0.75, what profit rent is
  shown by the transaction? Profit rent is the
  rental value minus the rent paid.
• Function required PMT(rate, nper, pv, fv, type)
• The following formula returns 5,680.95
• PMT(0.75,48,-230000,0,1)

6
Complex Discounting Problems

• EXAMPLE 5
• If I discount at 0.75 per month, how much should
  I pay for a property yielding 25,000 per month
  in advance (which I estimate will be worth
  5,000,000 in five years)?
• Function required PV(rate, nper, pmt, fv, type)
• The following formula returns 4,406,865.34
• PV(0.75,60,25000,5000000,1)
• This example uses a rate per month, and payments
  are monthly. Therefore, the nper argument has
  been converted to months.
• We can check this calculation by using the RATE
  function. The following formula returns 0.75
• RATE(60,25000,-4406865.34,5000000,1)

7
Complex Discounting Problems

• EXAMPLE 6
• I paid 1,200,000 for a property that yields a
  rent of 12,000 per month in advance. If I sell
  it in five years for 1,500,000, what yield will
  I receive?
• Function required RATE(nper, pmt, pv, fv, type,
  guess)
• The following formula returns 1.29136
• RATE(60,12000,-1200000,1500000,1)
• This result can be verified by using the PV
  function. The following formula returns
  1,200,000.00
• PV(1.29136,60,12000,1500000,1)

8
Complex Discounting Problems

• EXAMPLE 7
• A property has been purchased for 1,600,000. It
  yields a rent of 10,000 per month in advance. If
  I am to secure a yield of 1 per month, what must
  the property be worth in five years when I plan
  to sell it?
• Function required FV(rate, nper, pmt, pv, type)
• This formula returns 2,081,851.05
• FV(1,60,10000,-1600000,1)
• This result can be verified using the following
  formula (which returns 1,600,000)
• PV(1,60,10000,2081851.05,1)

9
Referensi

• Walkenbach, John. 2001. Excel 2002 Formulas. New
  York MT BooksAn imprint of Hungry Minds, Inc.
• Slide ini dapat didownload di
• http//andiwijayanto.blog.undip.ac.id