Transcript Discounting Problems

Discounting Problems
Andi Wijayanto, S.Sos, M.Si
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)
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)
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)
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)
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)
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)
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)
Referensi
 Walkenbach, John. 2001. Excel 2002 Formulas. New
York: M&T BooksAn imprint of Hungry Minds, Inc.
 Slide ini dapat didownload di:
http://andiwijayanto.blog.undip.ac.id