Leasing advance payments

[Nick Feasey]

Hi,
Can anybody help? How do you write into the PV or IRR
calctake account of more than one advance rental using the
PMT function?

For example a 36 monthly lease with 3 payments in advance
(3 + 33 profile). I can work this on a cashflow but need
assistance on how to formulise this. Your help much
appreciated. Kind regards, Nick

 

[Paul Corrado]

Nick,

Since the PV of the advance payments is the sum of the payments

PV("rate","term"-"#advance","pmt")+"pmt"*3

would work.

IRR, or any other yield/rate function, would not work, as there are no
negative cash flows (in the case you have described) and would thus be
infinite.

PC

 

[Norman Harker]

Hi Nick!

Let me re-phrase the question as I may have got it wrong:

3 months rent paid up front
Each month thereafter 1 month rent paid.
Total payments are 36
Payment "holiday" for final 3 months of lease period.

Hard coding in rate of 7% nominal compounded monthly and payments of
$100 per month:

This gives me for PV
=PV(7%/12,33,-100,0,0)+(3*100)
Returns 3293.901

Alternative:
=PV(7%/12,34,-100,0,1)+(2*100)
Returns 3293.901

Cross check of rate:

=RATE(33,-100,3293.901-(3*100),0,0,0)*12
Returns: 7%

By setting up the cash flow of:

A1:
-300
A2:A34
-100
=NPV(7%/12,A2:A34)+A1
Returns: 3293.901

Reversing sign of NPV and adding to the first cash flow i get revised
cash flow:

B1
=A1-(NPV(7%/12,A2:A34)+A1) [returns 2993.90]
B2:B34
-100

=IRR(B1:B34,0)*12
Returns: 7%

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 21st July: Belgium (National Day),
Bolivia (Martyrs' Day), Guadeloupe (Victor Schoelcher Day), Guam
(Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
(Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.