Guru Stocks at 52-Week Low: HSBC Holdings plc, Duke Energy Corp, Transocean Inc, Hitachi Ltd, CSX Corp.http://www.gurufocus.com/news.php?id=42313

The market remained almost flat last week, gaining a couple of points. Last week’s top five out of favor companies that reached their 52-week lows were HSBC Holdings plc, Duke Energy Corp, Transocean Inc, Hitachi Ltd, and CSX Corp.

Last week’s top two out of favor industries were the Oil Equipment Services and Distribution and the Personal and Household goods industries. 11 Oil Equipment companies reached their 52-week lows, while 2 reached their 52-week highs, giving the industry a low/high ratio of 5.5. In the Personal and Household Goods industry, 26 stocks have reached their 52-week lows, while 5 have reached their 52-week highs, giving the industry a low/high ratio of 5.2.

For full details about guru stocks at 52-week lows and more information, click here

HSBC Holdings plc (HBC)

Reached the 52-Week Low of $45.55 The prices of HSBC Holdings plc (HBC) shares have declined to close to the 52-week low of $45.55, which is 48.0% lower than its 52-week high of $87.67. HSBC Holdings plc is owned by 8 Gurus we are tracking. Among them, 1 has added to their positions during the past quarter. 5 reduced their positions.

HSBC HOLDINGS is one of the largest banking and financial services organizations in the world. The company has a market cap of $124.19 billion; its shares were traded at around $45.55 with a P/E ratio of 53.5 and P/S ratio of 0.85. The dividend yield of HSBC Holdings plc stocks is 8.49%. HSBC Holdings plc had an annual average earning growth of 36.9% over the past 5 years.

Richard Pzena owns 115,665 shares as of 09/30/2008, which accounts for 0.07% of the $13.21 billion portfolio of Pzena Investment Management LLC. Ruane Cunniff owns 3,725 shares as of 09/30/2008, a decrease of 77.73% of from the previous quarter. This position accounts for less than 0.01% of the $9.47 billion portfolio of Ruane & Cunniff & Goldfarb Inc.

Duke Energy Corp. (DUK)

Reached the 52-Week Low of $14.68 The prices of Duke Energy Corp. (DUK) shares have declined to close to the 52-week low of $14.68, which is 30.2% lower than its 52-week high of $20.66. Duke Energy Corp. is owned by 5 Gurus we are tracking. Among them, 1 has added to their positions during the past quarter. 4 reduced their positions.

Duke Energy Corporation is an integrated energy and energy services provider with the ability to offer physical delivery and management of both electricity and natural gas throughout the U.S. and abroad. The company. has a market cap of $18.49 billion with a P/E ratio of 11.9 and P/S ratio of 1.45. The dividend yield of Duke Energy Corp. stocks is 6.29%.

Duke Energy Corp. recently reported its third quarter 2008 results. The company announced that its EPS was 33 cents per share, compared to 45 cents per share in the third quarter of 2007. Commenting on the company’s results, Duke’s CEO said, "We are disappointed in the third quarter results, but our strong performance earlier in the year will help mitigate the impact of these results on our year-end performance. Although we don't believe we will be able to achieve our 2008 employee incentive target of $1.27 of adjusted diluted earnings per share by year's end - particularly in this economy -- we are only about 5 cents off of where we expected to be at the end of the third quarter."

Kenneth Fisher owns 20,453 shares as of 09/30/2008, which accounts for less than 0.01% of the $31.63 billion portfolio of Fisher Asset Management, LLC. David Dreman owns 28,850 shares as of 09/30/2008, a decrease of 41.18% of from the previous quarter. This position accounts for less than 0.01% of the $10.85 billion portfolio of Dreman Value Management. Bruce Berkowitz sold out his holdings in the quarter that ended on 09/30/2008.

Grp Exec & CFO David L Hauser sold 6,000 shares of DUK stock on 09/19/2008 at the average price of $18.8; the price of the stock has decreased by 21.91% since.

Transocean Inc. (RIG)

Reached the 52-Week Low of $46.72 The prices of Transocean Inc. (RIG) shares have declined to close to the 52-week low of $46.72, which is 71.1% lower than its 52-week high of $161.4.

Transocean Inc. is owned by 8 Gurus we are tracking. Among them, 2 have added to their positions during the past quarter. 6 reduced their positions. Transocean Inc. provides offshore contract drilling services for oil and gas exploration development and production. The company has a market cap of $18.11 billion, a P/E ratio of 3.8 and P/S ratio of 2.84. Transocean Inc. had an annual average earning growth of 6% over the past 10 years.

Transocean Inc recently reported its third quarter 2008 financial results and the results of the first three months of 2008. The company announced that its net income for the first three months of 2008 was $3.44 per share, compared to $4.63 per share for the first three quarters of 2007. However, the company’s revenue grew significantly.

Kenneth Fisher owns 3,451,773 shares as of 09/30/2008. Chris Davis owns 6,787,729 shares as of 09/30/2008. Mark Hillman owns 206,842 shares as of 09/30/2008, a decrease of 33.01% of from the previous quarter. David Dreman owns 3,970 shares as of 09/30/2008, a decrease of 41.36% of from the previous quarter.

Hitachi Ltd. (HIT)

Reached the 52-Week Low of $41.43 The prices of Hitachi Ltd. (HIT) shares have declined to close to the 52-week low of $41.43, which is 46.4% lower than its 52-week high of $77.23. Hitachi Ltd. is owned by 3 Gurus we are tracking. Among them, 0 have added to their positions during the past quarter. 3 reduced their positions.

Hitachi Ltd. headquartered in Tokyo is one of the world's leading global electronics companies. They manufacture and market a wide range of products including computers semiconductors consumer products and power and industrial equipment. It has a market cap of $14.92 billion; its shares were traded at around $41.43 with and P/S ratio of 0.13. The dividend yield of Hitachi Ltd. stocks is 1.09%. Hitachi Ltd. had an annual average earning growth of 10.9% over the past 5 years.

Dodge & Cox owns 14,220,319 shares as of 09/30/2008, which accounts for 1.09% of the $90.25 billion portfolio of Dodge & Cox. Kenneth Fisher owns 3,490 shares as of 09/30/2008, a decrease of 46.83% of from the previous quarter. This position accounts for less than 0.01% of the $31.63 billion portfolio of Fisher Asset Management, LLC. Charles Brandes owns 2,787,108 shares as of 09/30/2008, a decrease of 53.99% of from the previous quarter. This position accounts for 0.7% of the $27.58 billion portfolio of Brandes Investment.

CSX Corp. (CSX)

Reached the 52-Week Low of $31.6 The prices of CSX Corp. (CSX) shares have declined to close to the 52-week low of $31.6, which is 54.2% lower than its 52-week high of $69.06. CSX Corp. is owned by 3 Gurus we are tracking. Among them, 1 has added to their positions during the past quarter. 3 reduced their positions.

CSX Corporation's unique combination of rail container-shipping inter modal and logistics services provides global reach that's second to none. The company's goal advanced at each of its business units is to provide efficient competitive transportation and related services for customers. It has a market cap of $12.56 billion; its shares were traded at around $31.6 with a P/E ratio of 8.7 and P/S ratio of 1.25. The dividend yield of CSX Corp. stocks is 2.76%. CSX Corp. had an annual average earning growth of 12.8% over the past 10 years.

CSX recently reported its third quarter earnings from continuing operations. The company announced thats its earnings per share (EPS) was up 40%. Revenue was also up 18%, while operating income rose 31%. CSX delivered impressive financial results in a challenging economy,” said the company’s CEO, president, and chairman. “Our resilient business portfolio and disciplined operations continue to generate substantial earnings growth for shareholders.”

Kenneth Fisher owns 507,382 shares as of 09/30/2008. Dodge & Cox owns 27,900 shares as of 09/30/2008. Chuck Akre owns 17,769 shares as of 09/30/2008. Ken Heebner sold out his holdings in the quarter that ended on 09/30/2008.

Chairman, President & CEO, Director Michael J Ward sold 781,920 shares of CSX stock on 11/05/2008 at the average price of $46.46; the price of the stock has decreased by 31.98% since.

Buffett Says Credit Crisis Ebbs for Wall Street Firms

Buffett Says Credit Crisis Ebbs for Wall Street Firms (Update4)
By Josh P. Hamilton and Betty Liu

May 3 (Bloomberg) -- Warren Buffett, chief executive officer of Berkshire Hathaway Inc., said the global credit crunch has eased for bankers, and the Federal Reserve probably averted more failures by helping to rescue Bear Stearns Cos.
``The worst of the crisis in Wall Street is over,'' Buffett said today on Bloomberg Television. ``In terms of people with individual mortgages, there's a lot of pain left to come.'' Buffett was interviewed before the Omaha, Nebraska-based company's annual meeting, attended by about 31,000 people.
Buffett, the world's richest man according to Forbes magazine, said the Fed acted properly when it arranged a $2.4 billion buyout in March of New York-based Bear Stearns by JPMorgan Chase & Co. The billionaire said he turned down the opportunity because he lacked enough capital and time to craft a solution. More failures and wider panic may have resulted if the regulators didn't halt the run on Bear Stearns, he said.
``The worry was that there would be contagion; it was a very real worry,'' Buffett said. ``If Bear Stearns had gone, the next day, somebody else would have gone. It could've been a very, very, very chaotic situation.''
Buffett, 77, said he was contacted in March before JPMorgan, the third-biggest U.S. bank by assets, agreed to buy Bear Stearns. The person calling him, whom he wouldn't identify, was ``someone responsible'' and wasn't from the Federal Reserve or the Treasury. The call lasted about half an hour, Buffett said.
Too Big for Buffett
``As I understand it, Bear Stearns had $65 billion due on Monday and I didn't have $65 billion,'' Buffett said. ``I couldn't get my mind around that situation in the required time.'' New York-based JPMorgan was the right buyer for Bear Stearns, he added.
Berkshire had about $35 billion in cash as of March 31, according to a regulatory filing yesterday.
JPMorgan agreed in mid-March to acquire Bear Stearns, once the fifth-biggest U.S. securities firm, after customers grew concerned about the company's health and pulled out their money, leaving Bear Stearns short on cash. JPMorgan, which got financial support from the Federal Reserve, raised the purchase price a week later to $10 a share from $2 to mollify Bear Stearns shareholders who said they weren't getting enough.
The 24-company KBW Bank Index has advanced 14 percent since the Bear Stearns bailout was announced in March, and the 11- company Amex Securities Broker/Dealer Index has climbed 30 percent.
Credit Losses
In a question-and-answer session at the shareholder meeting, Buffett said that from a risk perspective, some banks got ``too big to manage.''
The world's largest banks and investment firms have recorded more than $300 billion of losses and writedowns tied to mortgages, bonds and loans.
Berkshire's own investment in derivative contracts recovered $500 million to $600 million of lost value since the end of March, Buffett said. The company will make ``significant money'' on the derivatives over the long term, he said at the meeting. Berkshire said yesterday the value of the investments had declined by $1.7 billion in the first quarter. The entire company's quarterly profit plunged 64 percent to $940 million.
Buffett is scheduled to embark on a four-city European trip this month to scout potential acquisitions, including family- owned companies. He has been investing in China, Israel and the U.K. to spur profit growth after saying that U.S. investments meeting his criteria have become scarce.
International Earnings
``Over time we'd like to develop more international earnings,'' Buffett said. ``If it's a $2 billion deal, fine; if it's a $20 billion dollar deal, fine.''
Buffett, who made his first non-U.S. acquisition in 2006, paying $4 billion for 80 percent of Israel-based Iscar Metalworking Cos., said he can't predict the location of the next company Berkshire will acquire.
``They can come from Europe, they can come from the United States, you just never know,'' he said. ``Somebody, someplace is going to have a situation where we fit. They're going to call me; I want to make sure I'm on their radar screen.''
Buffett said during the meeting he'd like to buy businesses in India and China, and that he wanted to acquire one or two non- U.S. companies in the next three years. He is looking as competition forces down insurance rates in the U.S. for Berkshire, which typically gets about half its profit from insurance units including National Indemnity, General Re Corp. and Geico Corp.
The U.S. dollar will keep weakening and Buffett feels ``no need to hedge'' against currency risk when buying large companies outside the U.S., he said.
Landing From Mars
``If I landed from Mars today with a billion of Mars dollars, or whatever they call them on Mars, and I was thinking about where to put my money,'' he said. ``I don't think I'd put the entire billion in U.S. dollars.''
Berkshire Hathaway has spent $4 billion investing in the municipal auction-rate bond market, taking advantage of payouts that topped 10 percent after regular bidders fled the market. Markets were so disrupted, Buffett said, that bonds from the same issue were selling simultaneously from the same broker with yields of 6 percent and 11 percent.
Berkshire has risen about 22 percent in New York Stock Exchange composite trading during the past 12 months and gained about 4,700 percent in 20 years through Dec. 31, about six times more than the Standard & Poor's 500 Index including dividends.
Buffett took shareholder questions for more than five hours on dozens of issues.
Other topics Buffett addressed include:
-- There's ``no guarantee'' Berkshire Hathaway won't be a buyout target after his death, though such a takeover is unlikely.
-- He said he's in good health because of his diet, ``some Wrigley, some Mars, some See's, some Coke.'' Berkshire this week committed $6.5 billion to help finance candy company Mars Inc.'s takeover of Wm. Wrigley Jr., the world's biggest maker of chewing gum. Berkshire owns See's Candies and is the top shareholder of Coca-Cola Co.
-- He doesn't support a push for companies or countries to boycott the Olympics in China based on that country's human rights record.
-- He would buy shares of PetroChina Co. again if they are at a level he considers cheap, Buffett said. Berkshire sold a stake in the company last year.
-- Factories in China have different norms for working conditions than those in the U.S., and he won't ``tell the world how to run'' their businesses.
To contact the reporters on this story: Josh P. Hamilton in Omaha at jphamilton@bloomberg.net; Betty Liu in New York at Bliu17@bloomberg.net; Last Updated: May 3, 2008 18:43 EDT

Don't trust the Wall St rally

Don't trust the Wall St rally

Divining future profitability of the nation's financial firms tells us stock market valuations are still too high.
By Bethany McLean, Editor at Large

Up until now, all eyes have been on the losses that are hitting the financial sector from the acronym soup of new instruments such as CDOs and SIVs. Everyone is scared, and rightly so, of the MUB (Monster Under the Bed) that might be lurking in supposedly safe havens. Still, financial stocks staged a big rally on the last trading day before the weekend, and again Monday, due to the belief that the worst is past, and that the government will step in to save the Street should that MUB pop out from under the bed.
But even once the current crisis is past, there's another issue facing the financial sector: Will it look like it used to? "I think it is important to step back and ask some broader questions about our financial system," wrote Ben Inker, the chief investment officer for quantitative equities in global developed markets at money management firm GMO, in a recent paper. "What it does, how big it should be; and what its sustainable level of profitability might be."
These questions are obviously important for financial services firms. At its recent peak stock price in December 2006, Citigroup (C, Fortune 500), for instance, sold for $53.34, or over 2 times its reported book value (and over 4 times if you exclude goodwill and intangibles) and almost 13 times its reported 2006 earnings. Do those numbers represent a baseline to which we'll return when this crisis has passed, or are they anomalies?
And the size of the financial sector may also matter for the rest of the market. In a piece last summer, credit rating agency Moody's opined that the market was safe from systemic risk in part because the $45 billion in profits reported by a group of financial firms including Citi and Merrill Lynch (MER, Fortune 500) were "considerable and significantly larger than in 1998," when those same firms reported profits of $12 billion. As the events surrounding Bear Stearns show all too clearly, the market isn't safe from systemic risk. Was Moody's wrong partly because that $45 billion isn't sustainable - or wasn't real in the first place?
One way to think about this is to look at the profits of the U.S. financial sector versus GDP. Inker did this, and the result was what he describes as a "truly striking chart." From 1947 to 1997, financial profits were stable at around 0.75% of GDP. But over the last ten years, the share of GDP represented by financial profits began to shoot higher. In the last few years - before the Street began to report massive writeoffs - financial profits represented roughly 2.25% of GDP. Inker says that it is too simplistic to say that the right number should be 0.75%. But when you think about what financial profits consisted of at the height of the boom, 2.25% seems unsustainable too.
The last decade saw the explosion of securitization - the carving up and redistributing of risk - the boom in hedge funds, and the private equity mania. It's apparent now that Wall Street can't transform a sub prime mortgage into a triple A credit, and that the redistribution of risk doesn't get rid of it. Unless (or until) we forget that simple lesson, it's hard to see the securitization game being played again. As for hedge funds, some commentators, such as Pimco's Bill Gross, predict the demise of broad swaths of them. With that goes the rich profits Wall Street has earned on prime brokerage. And fees from private equity, which at the height made up huge chunks of the Street's investment banking revenues? That won't come back roaring without cheap credit.
We are going to have to create whole new ways of securitizing and funding debt of all types, but especially mortgages and consumer credit. While I have confidence that those intrepid bankers on Wall Street will figure out something, as their future bonuses depend on it, it is going to take time to replace a system that took decades to build.
You also have to consider the massive writeoffs that the Street has taken. Thus far, Citigroup has taken $32 billion in writedowns related to the subprime crisis. Merrill Lynch's writedowns have totaled $22 billion. So were Citi's 2006 profits really the reported $21.2 billion, and were Merrill's the reported $7.5 billion? Or was some percentage of that an illusion? If Bear can be sold for $2 or $10 a share, then how solid was Bear Stearns' $84 per share in reported book value?
Thought about more broadly, if commentators are right that mortgage losses alone will total $300 billion to $500 billion, then, as Inker writes, "profits that look like they have been 2.25% of GDP in the past several years have actually been more like 1.75%, if we smooth the losses over the last 3 years and into next year as rough justice." And of course, mortgage losses are only a subset of the total losses.
Think back to what Ken Lewis, the CEO of Bank of America (BAC, Fortune 500), said last fall when his company announced its first round of writedowns: "Making money for several years, only to give most of it back in one year, is not a brilliant business model."
Inker says that the data doesn't point to any firm conclusions about what the level of financial profits should be. His best guess, though, is that a "normal" level of profits would be about half the amount that the financial sector reported in 2006.
Then, you also have to think about the multiple of those earnings that investors should be willing to pay. In a paper published in the fall of 2005, risk management gurus Leslie Rahl and Barbara Lucas of Capital Markets Risk Advisors, noted that in the past decade, a lot of things have happened that aren't supposed to happen, from the interest rate hikes of 1994 to the 1998 collapse of LTCM to the 2001 terrorist attacks. Or as the authors put it, "once-in-a-lifetime events seem to occur every few years."
If that's the case and if such events now mean that Bear Stearns (BSC, Fortune 500) can go from seemingly viable to threatening to bring down the entire financial system in the space of a week - then what sort of multiple should investors pay for Bear, or for any financial firm? Maybe investors shouldn't pay 12 times earnings, and maybe they should pay a discount to, rather than a multiple of, reported book value.
Of course, trying to guess how this will play out is just that - guessing. But if you say, for instance, that Merrill's normalized profits would be half the 2006 level, you get to about $4 billion. If you think that we should be willing to pay a smaller multiple for those earnings than we did in the past - let's be generous and say 10 times - then you get to a total market value for Merrill Lynch of $40 billion. That's still a 10% discount from today's valuation.

The Time vs. Task Dilemma: Why You Could Be Working Too Much

The Time vs. Task Dilemma: Why You Could Be Working Too MuchSkellie
One of the reasons many of us choose to start a freelance business is the option of largely escaping time-based payment. If a task only takes an hour, it takes an hour. People like us get paid the same whether we fill a day with it or not.
While freelancers who’ve made efforts to escape time-based pay get some pretty neat perks, there’s a trade-off: a heightened risk of over-work.
Unless they’re being given more work than they can feasibly do in the time, 20, or 40, or 70-hour per week workers don’t necessarily need to be more productive. For project-paid freelancers, the speed with which we can fly through tasks will dictate how financially successful we are.
And there’s the rub. There’s nobody telling us to go home at the end of the day, there’s no point beyond which our work is unpaid simply because it’s late in the evening, no time when the office lights start to go out, no pre-paid hours. We complete a task, we get paid, and we can complete most tasks at any time of the day or night, on any day of the week. It’s no surprise that many freelancers are overworked. The lure of “one more project, one more invoice” can be hard to resist.
The danger of overwork is compounded because project-paid freelancers have a habit of not keeping accurate tabs on the hours we work. If you enjoy what you do, working out exactly what to track can be a puzzle. Does feed reading count as play, or work-related research? What about answering emails — not all of which are strictly business related? The work-life divide is often a blur.
The problem
The only thing standing between 80 hour weeks is either a) a lack of projects or b) will-power. If you’re feeling overworked, you’ve probably got enough clients. The only variable left is self-control: the ability to say “I’ve worked enough today,” and stop. If you don’t yet have it, how do you get it?
The first question to answer is: do I feel overworked? It’s a gut feeling you get. Not necessarily all the time, but it will rear its head occasionally, maybe at the end of a day when you’ve worked from when you woke up until when you tumbled back into bed, or when you realize that you haven’t seen your best friend in a while. The next variable is how you react to that gut feeling. It’s all too easy to say: “But I need to be working this much right now, because of this, this and that.” In other words, if we overwork now, we can relax later. That ‘relaxed later’ is usually postponed ad-infinitum. Sound familiar?
One solution
This isn’t the only solution and I don’t claim that it will work for everyone. All I can say is that it worked for me, and my freelance routine probably isn’t much different to yours (liaise with clients, do work, invoice, get paid… eventually). Even if this solution won’t work right out of the box for you, it might be made workable with a few adaptations.
The process starts with a calculation: what’s the minimum amount you need to earn in a week in order to live? In other words, to pay rent, bills, buy food and have a little extra spending money left over — let’s say, $50. That’s not your ideal income, of course, but it’s the benchmark for your absolute Minimum Weekly Income (MWI) — the amount you must make to keep your affairs in order. You should only allow yourself to overwork in order to meet your MWI.
The next calculation is your cap: your Target Weekly Income (TWI). The formula is this: your average hourly rate multiplied by the number of hours you’re willing to work. Let’s say you’ve worked out your average hourly rate to be $30 and you want to spend 30 hours a week on paid tasks. Your TWI is $900. When working out the hours you want to work each week, I’d always suggest subtracting roughly 5 hours (or 2 hours for part-timers) to account for non-paid, work-related tasks (like managing accounts, answering email and liaising with clients). In this example, the person would be working 35 hours, and get paid for 30.
If you’re not sure of your average hourly rate, take the last month’s worth of jobs (or the last two-weeks worth if your memory is as bad as mine) and divide how much you got paid for each job by roughly how many hours the job took. Then add up the average hourly incomes for each job and divide by the total number of jobs over the time period. The result is a rough estimate of how much you earned per hour of work last month.
The purpose of the TWI is to establish a ceiling: the point where you stop working or accepting new jobs, even if you haven’t reached the maximum amount of hours you want to work in a week. Sometimes you will work less than full-time hours, but this is to balance out those weeks where you have to struggle and over-work just to meet your Minimum Weekly Income. Alternately, you can keep working past your TWI until your reach your work-cap for the week, but you should claim the time back as earned vacation time, or raise your TWI if you over-earn consistently.
To show you a working model, here’s my overwork safety net:
MWI = $300 (If I have to, I’ll exceed my work-cap to meet this minimum earn).
Work-cap = 10 hours (I’m finishing a communications degree and have a lot of other projects going on — I don’t want to do more than ten hours freelancing a week).
TWI = $500 (The weekly earn I aim for — I can stop working once I reach it even if I haven’t reached my work-cap).
Average hourly rate: $50 (I work fast, and I won’t accept jobs with a lower estimated hourly rate unless my MWI is in danger).
If you feel like you’re regularly exceeding your TWI while staying within your work-cap, it’s time to raise your TWI by increments.
As you can probably guess, this model does require some rough time-keeping but your career is still defined by income rather than hours. If we’re to be honest, we can’t avoid overworking unless we define what overwork means for us.
If you want to get started now and don’t mind sharing, you’re welcome to list your MWI, Work-cap, TWI and rough hourly rate in the comments section. I’d also be interested to hear your experiences with over-work. If you’ve conquered it, what was your strategy?

Web

Thanks to my buddy, Thomas. He gave me this link so that my blog can have 3 columns. :)

Link

Excel finance formula

For those who are interested in Excel financial formula.

Finance Functions

Introduction

Microsoft Excel provides a series of functions destined to perform various types of financially related operations. These functions use common factors depending on the value that is being calculated.

Many of these functions deal with investments or loan financing.

The Present Value is the current value of an investment or a loan. For a savings account, a customer could pledge to make a set amount of deposit on a bank account every month. The initial value that the customer deposits or has in the account is the Present Value. The sign of the variable, when passed to a function, depends on the position of the customer. If the customer is making deposits, this value must be negative. If the customer is receiving money (lottery installment, family inheritance, etc), this value should be positive.

The Future Value is the value the loan or investment will have when the loan is paid off or when the investment is over. For a car loan, a musical instrument loan, a financed refrigerator, a boat, etc, this is usually 0 because the company that is lending the money will not take that item back (they didn't give it to the customer in the first place, they only lend him or her some money to buy the item). This means that at the end of the loan, the item (such as a car, boat, guitar, etc) belongs to the customer and it is most likely still worth something.

As described above and in reality, the Future Value is the amount the item would be worth at the end. In most, if not all, loans, it would be 0. On the other hand, if a customer is borrowing money to buy something like a car, a boat, a piano, etc, the salesperson would ask if the customer wants to put a "down payment", which is an advance of money. Then, the salesperson or loan officer can either use that down payment as the Future Value parameter or simply subtract it from the Present Value and then apply the calculation to the difference. Therefore, you can apply some type of down payment to your functions as the Future Value.

The Number Of Periods is the number of payments that make up a full cycle of a loan or an investment.

The Interest Rate is a fixed percent value applied during the life of the loan or the investment. The rate does not change during the length of the Periods.

It is very important to understand how these two arguments are passed to a function. The period could be the number of months of a year, which is 12; but it could be another length. Suppose a customer is getting a car loan that would be financed in 5 years. This is equivalent to 5 * 12 = 60 months. In the same way, a cash loan can stretch from 0 to 18 months, a carpenter truck loan can have a life financing of 40 months, and a condominium can be financed for 15 years of 12 months plus an additional 8 months; this is equivalent to (15 * 12) + 8 = 188 months. Here is the tricky part, especially as far as Microsoft Excel deals with its finance functions. If you pass the number of Periods in terms of years, such as 5 for a car loan that stretches over 5 years, then you can pass the Rate as a percentage value, such as 8.75%. If you pass the number of Periods in terms of months, for example you can pass it as 44 for a car that is financed in 3 years and 8 months, then you must communicate this to the Rate argument by dividing the Rate by 12. In other words, a Rate of 8.75% would be passed as 8.75%/12. If the Rate was typed in a cell named B2 that displays 8.75%, you can pass it as B2/12.

For deposits made in a savings account, because their payments are made monthly, the rate is divided by the number of Periods of a year, which is 12. If an investment has an interest rate set at 14.50%, the Rate would be 14.50/12 = 1.208. Because the Rate is a percentage value, its actual value must be divided by 100 before passing it to the function. For a loan of 14.50% interest rate, this would be 14.50/12 = 1.208/100 = 0.012.

The Payment is the amount the customer will be paying. For a savings account where a customer has pledged to pay a certain amount in order to save a set (goal) amount, this would be the amount the customer would pay every month. If the customer is making payments (car loan, mortgage, deposits to a savings account, etc), this value must be negative. If the customer is receiving money (lottery installment or annuity, family inheritance, etc), this value must be positive.

The Payment Type specifies whether the payment is made at the beginning or the end of the period. For a monthly payment of an item financed like a car, a boat, a guitar, or a house this could be the end of every month.

The Future Value of an Investment

To calculate the future value of an investment, you can use the FV() function. The syntax of this function is:

FV(Rate, Periods, Payment, PresentValue, PaymentType)

Practical Learning: Calculating the Future Value

1. Start a new workbook and fill up Sheet1 as follows:


2. Save it as Business
3. Double-click Sheet1 to put its label into edit mode. Type Future Value and press Enter

4. Click cell C8 and, on the main menu, click Insert -> Function...

5. In the Paste Function dialog box, in the Function Category list, click Financial. In the Function Name list, double-click FV and move the FV window so you can see the values on the worksheet

6. Click the box to the right of Rate and, on the worksheet, click cell C5 and type /12
7. In the FV window, click the box to the right of Nper and, on the worksheet, click cell C7
8. In the FV window, click the box to the right of Pmt and type -
9. On the worksheet, click cell C6
10. In the FV window, click the box to the right of Pv and type -
11. On the worksheet, click cell C4
12. Since this is a loan, the payments are expected at the end of the month. Therefore, in the FV window, click the box to the right of Type and type 0
13. Click OK


The Number of Periods of an Investment


To calculate the number of periods of an investment or a loan, you can use the NPER() function. Its syntax is:

NPER(Rate, Payment, PresentValue, FutureValue, PaymentType);


Here is an example:




Investment or Loan Payment

The PMT() function is used to calculate the regular payment of loan or an investment. Its syntax is:

PMT(Rate, NPeriods, PresentValue, FutureValue, PaymentType)

In the following example, a customer is applying for a car loan. The cost of the car will be entered in cell C4. It will be financed at a rate entered in cell C6 for a period set in cell C7. The dealer estimates that the car will have a value of $0.00 when it is paid off.

Practical Learning: Calculating the Monthly Payments of a Loan

1. Double-click Sheet3 to put it in edit mode. Type Payments Amount and press Enter


2. Complete the worksheet as follows
3. Click cell C8 and type =PMT(
4. Click cell C6 and type /12,


5. Click cell C7 and type ,-

6. Click cell C4 and type ,

7. Click cell C5

8. Type ,0) and, on the Formula Bar, click the Enter button



9. Suppose that, during the evaluation, a customer decides that she doesn't need a brand new car anymore. Also, she thinks that a 5-year car loan is too long. Furthermore, she wants to make a $4500.00 down payment to reduce the monthly payments. On the other side of the desk, the salesperson who wants to make a juicy commission on this loan has decided to increase the interest rate. Change the new values of the worksheet as follows and see the result

10. Save the workbook

The Amount Paid As Interest During a Period

When a customer is applying for a loan, an investment company must be very interested to know how much money it would collect as interest. This allows the company to know whether the loan is worth giving. Because the interest earned is related to the interest rate, a company can play with the rate (and also the length) of the loan to get a fair (?) amount.

The IPMT() function is used to calculate the amount paid as interest on a loan during a period of the lifetime of a loan or an investment. It is important to understand what this function calculates. Suppose a customer is applying for a car loan and the salesperson decides (or agrees with the customer) that the loan will be spread over 5 years (5 years * 12 months each = 60 months). The salesperson then applies a certain interest rate.

The IPMT() function can help you calculate the amount of interest that the lending institution would earn during a certain period. In essence, you can use it to know how much money the company would earn in the 3rd year, or in the 4th year, or in the 1st year. Based on this, this function has an argument called Period, which specifies the year you want to find out the interest earned in.

The syntax of the IPMT() function is:

IPMT(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)

The Rate argument is a fixed percent value applied during the life of the loan.

The PresentValue is the current value of the loan or investment. It could be the marked value of the car, the current mortgage value of a house, or the cash amount that a bank is lending.

The FutureValue is the value the loan or investment will have when the loan is paid off.

The NPeriods is the number of periods that occur during the lifetime of the loan. For example, if a car is financed in 5 years, this value would be (5 years * 12 months each =) 60 months. When passing this argument, you must remember to pass the right amount.

The Period argument represents the payment period. For example, it could be 3 to represent the 3rd year of a 5 year loan. In this case, the IPMT() function would calculate the interest earned in the 3rd year only.

The PaymentType specifies whether the periodic (such as monthly) payment of the loan is made at the beginning (1) or at the end (1) of the period.

The FutureValue and the PaymentType arguments are not required.

Practical Learning: Calculating the Monthly Payments of a Loan

1. To add a new worksheet, on the main menu, click Insert -> Worksheet

2. Double-click the new Sheet1 tab to put it in edit mode. Type Periodic Interest Earned and press Enter

3. Move the new worksheet to be the most right
4. Complete the worksheet as follows

5. Click cell C9 and type =IPMT(

6. Click cell C5 and type /12,
7. Click cell C6 and type ,
8. Click cell C7 and type ,-
9. Click cell C4 and type ,
10. Click cell C8 and type ,
11. Type ,0) and, on the Formula Bar, click the Enter button
12. Save the workbook

The Amount Paid as Principal

While the IPMT() function calculates the amount paid as interest for a period of a loan or an investment, the PPMT() function calculates the actual amount that applies to the balance of the loan. This is referred to as the principal. Its syntax is:

PPMT(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)

The arguments are the same as described in the previous sections.

Practical Learning: Evaluating the Amount Paid As Principal

1. Change the Periodic Interest Earned worksheet as follows

2. Click cell C10 and type =PPMT(

3. Click cell C5 and type /12,

4. Click cell C6 and type ,

5. Click cell C7 and type ,-

6. Click cell C4 and type ,

7. Click cell C8 and type ,

8. Type ,0) and, on the Formula Bar, click the Enter button

9. Save the workbook

The Present Value of a Loan or an Investment

The PV() function calculates the total amount that a future investment is worth currently. Its syntax is:

PV(Rate, NPeriods, Payment, FutureValue, PaymentType)

The arguments are the same as described earlier.

The Interest Rate

Suppose a customer comes to a car dealer and wants to buy a car. The salesperson would first present the available cars to the customer so the customer can decide what car he likes. After this process and during the evaluation, the sales person may tell the customer that the monthly payments would be $384.48. The customer may then say, "Wooooh, I can't afford that, man". Then the salesperson would ask, "What type of monthly payment suits you". From now on, both would continue the discussion. Since the salesperson still wants to make some money but without losing the customer because of a high monthly payment, the salesperson would need to find a reasonable rate that can accommodate an affordable monthly payment for the customer.

The RATE() function is used to calculate the interest applied on a loan or an investment. Its syntax is:

RATE(NPeriods, Payment, PresentValue, FutureValue, PaymentType, Guess)

All of the arguments are the same as described for the other functions, except for the Guess. This argument allows you to give some type of guess for a rate. This argument is not required. If you omit it, its value is assumed to be 10.

Practical Learning: Calculating the Interest Rate

1. To add a new worksheet, on the main menu, click Insert -> Worksheet
2. Double-click the new Sheet1 tab to put it in edit mode. Type Interest Rate and press Enter
3. Move the new worksheet to be the most right
4. Change the Interest Rate worksheet as follows

5. Click cell C8 and type =-RATE(
6. Click cell C7 and type ,
7. Click cell C6 and type ,-
8. Click cell C4 and type ,
9. Click cell C5 and type ,0)*12 and, on the Formula Bar, click the Enter button
10. To use the ABS() function, change the function in cell C14 to =ABS(RATE(C7,C6,-C4, C5, 0)*12) and press Enter
11. Save the workbook

The Internal Rate of Return

The IRR() function is used to calculate an internal rate of return based on a series of investments. Its syntax is:

IRR(Values, Guess)

The Values argument is a series (also called an array or a collection) of cash amounts that a customer has made on an investment. For example, a customer could make monthly deposits in a savings or credit union account. Another customer could be running a business and receiving different amounts of money as the business is flowing (or losing money). The cash flows don't have to be the same at different intervals but they should (or must) occur at regular intervals such as weekly (amount cut from a paycheck), bi-weekly (401k directly cut from paycheck), monthly (regular investment), or yearly (income).

The Values argument must be passed as a collection of values, such as a range of selected cells, and not an amount. Otherwise you would receive an error.

The Guess parameter is an estimate interest rate of return of the investment.

Practical Learning: Calculating the Internal Rate of Return

1. To add a new worksheet, on the main menu, click Insert -> Worksheet
2. Double-click the new Sheet1 tab to put it in edit mode. Type Internal Rate of Return and press Enter
3. Move the new worksheet to be the most right
4. Change the worksheet as follows

5. Click cell D12 and type =IRR(
6. Select cells D4:D10 and, on the Formula Bar, click the Enter button
7. In cell D11, type 12 and click cell D12
8. In the Formula Bar, change the function to =IRR(D4:D10, D11) and press Enter (you shouldn't need any significant difference unless you change the range of cells such as D4:D8)
9. Save the workbook

The Net Present Value

The NPV() function uses a series of cash flows to calculate the present value of an investment. Its syntax is:

NPV(Rate, Value1, Value2, ...)

The Rate parameter is the rate of discount during one period of the investment.

As the NPV() function doesn't take a fixed number of arguments, you can add a series of values as Value1, Value2, etc. These are regularly made payments for each period involved. Because this function uses a series of payments, any payment made in the past should have a positive value (because it was made already). Any future payment should have a negative value (because it has not been made yet).

Practical Learning: Calculating the Net Present Value

1. To add a new worksheet, on the main menu, click Insert -> Worksheet

2. Double-click the new Sheet1 tab to put it in edit mode. Type Net Present Value and press Enter

3. Move the new worksheet to be the most right

4. Change the worksheet as follows

5. Click cell C14 and type =-NPV(

6. Click cell C13 and type ,

7. Select cells C4:C12 and, on the Formula Bar, click the Enter button

8. To use the ABS() function, change the function in cell C14 to =ABS(NPV(C13,C4:C12)) and press Enter

9. Save the workbook

How to create Gannt chart

In the event that you do not have MS Project, there is this person who is able to create Gannt chart from Excel! See the tricks below!