Leasing advance payments

N

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
 
P

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
 
N

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).
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top