Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR&NPV Financial lease
Financial lease calculations. There is an asset of fair value @ 3,512,000. There are 4 payments – 150,000 quarterly and yet the last one (after year) is purchase of this asset @ 3,200,000. Quaterly payments are made in advance. To record the value I need to use min of either fair value or NPV of MLP. First of all, I need to calculate IRR, then MLP based on IRR. A colleague of mine told me that because of advance payments IRR should be calculated as IRR(3,362,000…150,000…150,000…150,000…3,200,000) saying that initial fair value should be decreased by 150,000. But as I understand it should be like IRR(3,512,000…150,000…150,000…150,000…150,000…3,20 0,000) Appreciate if you can advise on these functions and correct lease treatment since I need to calculate impact on P&L & BS of finance charge and reduction in obligations. Unfortunetely never met with financial lease. If you know any useful links on this issue …I’ll be obliged to you for them. Thanks, Littleps -- littleps ------------------------------------------------------------------------ littleps's Profile: http://www.excelforum.com/member.php...o&userid=25482 View this thread: http://www.excelforum.com/showthread...hreadid=526304 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR&NPV Financial lease
IRR will work as long as you properly sign the cash flows. For example:
=irr(-3512000,150000,150000,150000,150000,3200000) Yields a return of 1.73%. The above flows would assume the balloon payment is made 15 months after the initial lease. If the balloon payment is supposed to be 12 months after the lease (ie, coincident with the last quarterly payment), you would use: =irr(-3512000,150000,150000,150000,3350000) Also, you might want to look into the XIRR function (part of the Analysis Tookpak addin). I find it a lot easier to use, because you simply specify the cash flow and the date on which it occurred. -- Regards, Fred "littleps" wrote in message ... Financial lease calculations. There is an asset of fair value @ 3,512,000. There are 4 payments - 150,000 quarterly and yet the last one (after year) is purchase of this asset @ 3,200,000. Quaterly payments are made in advance. To record the value I need to use min of either fair value or NPV of MLP. First of all, I need to calculate IRR, then MLP based on IRR. A colleague of mine told me that because of advance payments IRR should be calculated as IRR(3,362,000.150,000.150,000.150,000.3,200,000) saying that initial fair value should be decreased by 150,000. But as I understand it should be like IRR(3,512,000.150,000.150,000.150,000.150,000.3,20 0,000) Appreciate if you can advise on these functions and correct lease treatment since I need to calculate impact on P&L & BS of finance charge and reduction in obligations. Unfortunetely never met with financial lease. If you know any useful links on this issue .I'll be obliged to you for them. Thanks, Littleps -- littleps ------------------------------------------------------------------------ littleps's Profile: http://www.excelforum.com/member.php...o&userid=25482 View this thread: http://www.excelforum.com/showthread...hreadid=526304 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR&NPV Financial lease
"littleps" wrote:
Financial lease calculations. First, given the obvious professional liability of the situation, it behooves you to continue to seek professional assistance from a known-competent source. You should not assume that anyone in cyberspace is competent or is who or what they say they are. You have obviously already tried that by talking to a colleague. It might be worthwhile to pay for some advice from a CPA or similar professional. You can probably write off the expense of the consultation. I can tell you that I have no experience with financial leases. There is an asset of fair value @ 3,512,000. There are 4 payments €“ 150,000 quarterly and yet the last one (after year) is purchase of this asset @ 3,200,000. Quaterly payments are made in advance. [....] A colleague of mine told me that because of advance payments IRR should be calculated as IRR(3,362,000€¦150,000€¦150,000€¦150,000€¦3,20 0,000) saying that initial fair value should be decreased by 150,000. But as I as I understand it should be like IRR(3,512,000€¦150,000€¦150,000€¦150,000€¦150, 000€¦3,200,000) Both answers are correct, depending on details that you fail to provide. See my questions below. Your problem seems similar to an example in the HP 12C Owner's Handbook (p.177, Rev E, c. 1982). You might look there for more guidance. In either case, one important fix: the first term should be negative: -3,512,000 or -3,362,000. Some other details to take into consideration when formulating the exact IRR expression: 1. You wrote "4 payments €“ 150,000 quarterly and yet the last one (after year) is purchase of this asset @ 3,200,000". That sounds like 3 quarterly payments of $150,000 and a 4th quarterly(?) payment of $3,200,000. That is __not__ what your IRR formuation does. Your IRR formulation has 4 quarterly payments of $150,000 and a 5th (quarterly?) payment of $3,200,000. Your colleague's formulation has 3 quarterly payments of $150,000 and a 4th quarterly(?) payment of $3,200,000. 2. It is unclear from your English description and from your IRR "pseudocode" (i.e. not exactly written in Excel form) whether the quarterly payments are at the end or beginning of each quarter. Your formulation suggests that the 1st $150,000 is paid at the end of the 1st quarter. If, instead, the 1st $150,000 is paid at the beginning of the lease period, the first cash flow should be -3,512,000+150,000 = -3,362,000. In fact, that might what your colleague had in mind. 3. It is unclear from your English description and from your IRR pseudocode whether the $3,200,000 is paid in the same period of time as each of the other payments. You wrote "after year". That sounds like the beginning or end of the 13th month after beginning of the lease. If that is the case, use XIRR (for uneven payment periods), or if you use IRR, be sure to use the same period of time for each cash flow -- for example, monthly -- using zeros to fill in periods between quarterly payments. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR&NPV Financial lease
Thank you for your replies. Actually as per agreemnt the folowing info is known: Cost of the asset is 3,512,000 at the beginning of the lease. Inception of the lease is let's say 01.01.2006 for convenience. 4 payments are to be made IN ADVANCE. It means that e.g. 01.01.2006 - 150,000; 01.04.2006 - 150,000; 01.07.2006 - 150,000; 01.10.2006 - 150,000. Then it says that the assset will be purchased on annivesary @ 3,200,000. My understanding is on Jan'07. The problem is correct calculation of internal rate of return (IRR). Based on this percentage rate I'll be able to pick out finance charge out of each pmnt. Thanks. -- littleps ------------------------------------------------------------------------ littleps's Profile: http://www.excelforum.com/member.php...o&userid=25482 View this thread: http://www.excelforum.com/showthread...hreadid=526304 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR&NPV Financial lease
Now that we have the data, calculating the result is easy. Your cash flow is as
follows: 01/01/06 -3,362,000 04/01/06 150,000 07/01/06 150,000 10/01/06 150,000 01/01/07 3,200,000 Put those numbers in a table. Feed them to XIRR, as in =xirr(b1:b5,a1:a5). It will give you your answer (9.17%) Alternatively, you can use IRR because your cash flows happen at regular intervals. Remember, however, that the rate of return that IRR gives you is the *periodic* return. As your payments are quarterly, IRR will calculate a quarterly return. If you want an annual return, you will need to convert it. When you compare the two, you'll find a difference in the third decimal because your payments are not exactly the same number of days apart. That's why most people like XIRR. Have we solved your problem for you? -- Regards, Fred "littleps" wrote in message ... Thank you for your replies. Actually as per agreemnt the folowing info is known: Cost of the asset is 3,512,000 at the beginning of the lease. Inception of the lease is let's say 01.01.2006 for convenience. 4 payments are to be made IN ADVANCE. It means that e.g. 01.01.2006 - 150,000; 01.04.2006 - 150,000; 01.07.2006 - 150,000; 01.10.2006 - 150,000. Then it says that the assset will be purchased on annivesary @ 3,200,000. My understanding is on Jan'07. The problem is correct calculation of internal rate of return (IRR). Based on this percentage rate I'll be able to pick out finance charge out of each pmnt. Thanks. -- littleps ------------------------------------------------------------------------ littleps's Profile: http://www.excelforum.com/member.php...o&userid=25482 View this thread: http://www.excelforum.com/showthread...hreadid=526304 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR&NPV Financial lease
Fred, Thank you for your time. It's more clear to me now. That background will be useful for me. Actually, at first I even didn't know about xirr, xnpv functions. Regards, littleps -- littleps ------------------------------------------------------------------------ littleps's Profile: http://www.excelforum.com/member.php...o&userid=25482 View this thread: http://www.excelforum.com/showthread...hreadid=526304 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Financial modelling from Excel to Access | Excel Discussion (Misc queries) | |||
How do I get Excel to automatically calculate salaries actually received in financial year? | Excel Discussion (Misc queries) | |||
Revolver (Revolving Credit) Financial Modelling | Excel Discussion (Misc queries) | |||
Financial projections calendar in a Outlook-style for ease of use | Excel Discussion (Misc queries) | |||
How do I fix a circular reference in a financial statement? | Excel Discussion (Misc queries) |