In over my head - Net present value

[crunchtime9999]

Present value - This is the variable that needs to be solved for
Original lease amount (original equipment cost) - is known
Payment - known
Cost of funds / debt rate - known
Term - known
Future value - known
The leases are always billed in advance.

1 or 2 payments in advance - This variable is known but changes from one deal to another. it depends on whether we get a payment in advance or not.

Rental period prior to lease starting - this period can be from 1-90 days and needs to considered when calculating the profit in the deal. This will in essence added to the term. ie. if the rental period is 15 days on a 36 month lease then the term could be 36 months plus 15 days. The way our leases work is that they begin at the beging of each quarter. So say for example a lease funded on July 5th. They would pay a rental period until the lease actually started on October 1st. That would equate to 85 days of rent. The daily rental rate is simply 30 divided by the payment amount.

Once you come up with the PV using all of these different variable we would subtract the PV from the actual loan amount to come up with the profit in the deal.

I got this request from my boss and I have not done this sort of excel work. Can anyone help with how I need to set this up and the formulas.

 

[riaz]

Have a look at the financial functions built into excel. They cater for PV, NPV, INT, PMT and all shades of grey in between.

If you have not used these before, I suggest you use numbers that you have already calculated from a previous deal, so you can see which function gives you what you would expect. From there, its a simple matter to enter the function, feed the values and sit back.

Good luck.