On Thursday, December 19, 2013 8:53:21 AM UTC+5, Abraham A wrote:
Bob, good and evil resides in all of us. It is our choice to select one of them. Remember that company called Google that calls itself "epitome of all good" but you know and I know that such a claim is nothing more than the cover for Vanity Fair magazine.
Abraham, that's what you call The Devil's own.
Abe, Season's Greetings to you and the other financial engineers at the tad Portal
http://thinkanddone.com
New Year is just 8 days ahead before those down under at the Sydney Opera House ring in the year 2014 with fireworks and bottles of champaign in hot weather.
But I would rather enjoy a White Christmas at a farm house in New England that reminds me of the following parody of Robert Frost's famous poem keeping in view that we are analyzing investments using the tadXL add-in v3.0 for Excel 2007, 2010 and 2013.
Whose banks these are, I think I know
His house is in the village though
He will not see me stopping here
To watch his bank vaults fill up with dough
- Bobby Frost
Abe! Wishing you a Merry Christmas and a Happy New Year that will bring in profits that will offset your previous losses that were incurred in the summer of 2013.
Abe, I noticed that in the last few days you have extended the functionality of your Excel NPV function
http://tadxl.com/excel_npv_function.html and Excel IRR function
http://tadxl.com/excel_irr_function.html to include the effect of inflation on forward looking cash flow amounts. Not only your tadNPV function allows for a growth rate but it also permits you to use annuity payments that are worth their face value or those that are adjusted for inflation.
There are a lot more variables in your tadNPV function now as compared to what you had just a week ago. The following shows the syntax for this Excel NPV function that now accepts up to 12 different set of values. These values may be entered as single numbers or an array of numbers thus making the input array a 12 x N matrix (recall that Matrix is the code name for tadXL v3.0)
=tadNPV( rate, growth, tax_rate, cash flows, adjust_for_inflation, frequency, type, compounding, period, concentration, hair_cut, rate_rigged_by )
And one may either enter the values for this Excel NPV function as a range of cells such as
=tadNPV( B1:D1, B2:D2, B3:D3, B4:D4, B5:D5, B6:D6, B7:D7, B8:D8, B9:D9, B10:D10, B11:D11, B12:D12 )
or one may enter the values for this Excel net present value function as an array of numbers such as
=tadNPV( {0.15, 0.15, 0.15}, {0.1, 0.1, 0.1}, {0.4, 0.4, 0.4}, {1, 1, 1}, {0, 0, 0}, {50, 50, 50}, {0, 0, 0}, {1, 1, 1}, {1, 1, 1}, {1, 1, 1}, {0.25, 0.25, 0.25}, {0.2, 0.2, 0.2} )
All of these options are documented here at
http://tadxl.com the home page of tadXL add-in v3.0
I took three annuities in amount of $1 each that all made 50 different payments one after the other thus there were 150 payments in total each in amount of $1
Then I tested your Excel NPV function
http://tadxl.com/excel_npv_function.html with a combination of input values to find net present value of such annuities using the face value of $1 or the inflation adjusted $1. The discount rate of 15% was used for discounting the cash flows. A 10% growth rate was applied to these calculations. A tax rate of 40% was due on the annuity payments. A hair cut of 25% was used in some of these calculations. And I rigged the discount rate by 40% to see the difference in net present value.
Oh Boy! I was amazed at the options that you have now built into the tadNPV function that makes the foundation for tadIRR function that finds the internal rate of return.
The first set of data shown below using annuity amounts that have a face value of $1 throughout the different time periods. The first example uses no tax rate, no haircut nor any rigged rate. The NPV is found in amount of $17..849904
rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 0 0 0
cash_flow $1 $1 $1
adjust_for_inflation 0 0 0
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 0 0 0
rate_rigged_by 0 0 0
NPV = $17.849904
If the same set of values were used but we adjusted the $1 annuity payments for inflation, then the net present value of same payments in amount of $1 for 150 periods is $19.974575 which is higher than the sum of $17.849904 that we calculated earlier
This is the same value you would find by using present value interest factor of a growing annuity in amount of $1 using the tadPVIFGA function
Notice that this NPV is higher than the last NPV due to the use of inflation adjusted dollar amounts
rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 0 0 0
cash_flow $1 $1 $1
adjust_for_inflation 0 1 1
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 0 0 0
rate_rigged_by 0 0 0
NPV = $19.974575
If we go back to using the face value for $1 annuity and introduce a tax rate of 40% then the net present value is much higher than $17.849904 and it turns out to be $58.662327
rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 0 0
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 0 0 0
rate_rigged_by 0 0 0
NPV = $58.662327
But if we now switch to using inflation adjusted $1 along with a tax rate of 40% then the NPV results in an amount of $293.484911 which is much higher than the NPV of $19.974575 for the adjusted $1 amounts without the tax rate
rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 1 1
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 0 0 0
rate_rigged_by 0 0 0
NPV = $293.484911
Now if we use the unadjusted $1 amounts but take a 25% hair cut with 40% tax charge then the net present value turns out to be $43.996745 as compared to $58.662327 value without the hair cut.
rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 0 0
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 25% 25% 25%
rate_rigged_by 0 0 0
NPV = $43.996745
Taking a 25% hair cut on inflation adjusted $1 payments with a 40% tax rate returns an NPV of $220.113683 which is considerably lower for the same options without the haircut that amounted to $293.484911
rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 1 1
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 25% 25% 25%
rate_rigged_by 0 0 0
NPV = $220.113683
Now if we do not adjust for inflation and take a tax burden of 40%, along with a hair cut of 25% further rigging the rate by 40% the resulting net present value is $19.928734 that is much lower than the non-rigged NPV of $43.996745
rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 0 0
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 25% 25% 25%
rate_rigged_by 40% 40% 40%
NPV = $19.928734
Finally if we rig the rate by 40% and take a hair cut of 25% and pay a tax obligation of 40% on inflation adjusted $1 payments the NPV turns out to be $27.978728 compare this to the net present value of $220.113683 without the rigged rate.
rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 1 1
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 25% 25% 25%
rate_rigged_by 40% 40% 40%
NPV = $27.978728
Now you see how those bankers and traders at large lending banks make a quick buck when they rig the rate by a certain percentage that brings down the net present worth of payments that has to be paid to the clients who gave them their hard earned money all in hopes for return on investment - ROI :(