Question

A businessman purchased a large warehouse exactly three months ago for £4,350,000. The
businessman will spend a further £757,000 in refurbishments in a month’s time. An antique
dealer has just agreed to occupy the warehouse in nine months’ time. The dealer will pay rent to
the businessman immediately following the date of occupation for eight years and will then
immediately purchase the warehouse for £7,500,000. The rent in the first year has been set at
£640,000 per annum payable quarterly in advance. The agreement states that, at the start of
each subsequent year, the annual rent will increase by a fixed percentage on a compound basis.
Assuming that rental income is always received quarterly in advance, calculate (to two decimal
places) the compound percentage increase in the annual rent required in order to ensure that
the businessman earns a rate of return of 15% per annum effective on his investment in the
warehouse.

Answer 1

A B С D E F G I 1 2 Firstly we will assume annual increment to be 10%, and will construct a NPV schedule: 3 4 5 6 7 5 Assumed Inc Rate Cashflows 10% -51,07,000 $ -51,07,000 PV factor @15% 1.00000 Present Value $ -51,07,000 $ Particulars Initial Investment Rental Revenues Years: 8 1 1 9 10 11 12 2 3 4 5 6 7 $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ Net Present Value 6,40,000 7,04,000 7,74,400 8,51,840 9,37,024 10,30,726 11,33,799 12,47,179 0.86957 0.75614 0.65752 0.57175 0.49718 0.43233 0.37594 0.32690 13 14 15 16 17 $ $ $ $ $ $ $ $ $ 5,56,522 5,32,325 5,09,181 4,87,042 4,65,867 4,45,611 4,26,237 4,07,705 -12,76,510 00 8

A B с D E F G H 1 2 Firstly we will assume annual increment to be 10%, and will construct a NPV schedule: w N Assumed Inc Rate Cashflows 10% $ -51,07,000 $ -51,07,000 PV factor @15% 1.00000 Present Value $ -51,07,000 Particulars Initial Investment Rental Revenues Years: 1 $ 2 $ 4 5 6 7 8 9 10 11 12 13 14 15 16 17 3 4 $ 6,40,000 $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ Net Present Value 6,40,000 7,04,000 7,74,400 8,51,840 9,37,024 10,30,726 11,33,799 12,47,179 0.86957 0.75614 0.65752 0.57175 0.49718 0.43233 0.37594 0.32690 5 6 $ $ $ $ $ $ $ $ 5,56,522 5,32,325 5,09,181 4,87,042 4,65,867 4,45,611 4,26,237 4,07,705 -12,76,510 7 8 OC Now we will use Goal Seek function in excel to find the desired rate of Interest Goal Seek ? X Set cell: $G$17 19 20 21 22 23 24 25 26 27 0.0 To value: By changing cell: $E$5| 1 OK Cancel 28

A B с D E F G I 1 Firstly we will assume annual increment to be 10%, and will construct a NPV schedule: 2. 3 2 B Assumed Inc Rate Cashflows 19% $ -51,07,000 $ -51,07,000 5 PV factor @15% 1.00000 Present Value $ -51,07,000 Particulars Initial Investment Rental Revenues Years: 1 8 2 4 5 6 7 8 9 10 11 12 13 14 15 16 17 3 4 $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ $ 6,40,000 $ Net Present Value 6,40,000 7,64,595 9,13,447 10,91,278 13,03,728 15,57,538 18,60,761 22,23,014 0.86957 0.75614 0.65752 0.57175 0.49718 0.43233 0.37594 0.32690 5 6 $ $ $ $ $ $ $ $ $ 5,56,522 5,78,144 6,00,606 6,23,942 6,48,183 6,73,367 6,99,529 7,26,707 -0 7 8 Now we will use Goal Seek function in excel to find the desired rate of Interest Goal Seek Status ? X 18 19 20 21 22 23 24 25 26 27 Step Goal Seeking with Cell G17 found a solution. Target value: Current value: Pause 0 $-0 OK Cancel 28

At 19.47 % NPV becomes 0, therefore required rate of Return = 19.47%

In case of any issue, please comment below