I regularly receive emails from readers posing questions based on my previous posts on things like how to calculate NPV, using XIRR, and other financial and Excel questions.

One of the most common questions I receive is how to calculate a compound annual growth rate when the beginning value is negative.

The basic answer is that you can’t. Why?
Read the rest of this entry »

My philosophy on maintaining this blog site is very different from that of most bloggers. Rather than update it constantly with the latest happenings, I aim to write longer how-to explanations and tutorials that are less time-sensitive and provide useful reference material long after they’re written.

Though my posting rate has declined dramatically recently (this will improve soon), I still receive questions from readers, mainly about using Excel functions. Last week, I received one from a reader named Jae, who asked a Finance 101 question:

I have a problem in my finance class where the annual revenues from a project is $500,000 and annual costs are $300,000. The corporate tax rate is 40% and the cost of capital is 12%. How do I calculate NPV of the project?

The original problem has probably been paraphrased, and lacking other information, here’s how I’d interpret the problem. The project generates pre-tax revenues of $500K a year, and incurs operating costs of $300K a year. The corporate tax rate is 40%, the cost of capital is 12%, but nothing is provided about how long the project will last. For simplicity’s sake, let’s suppose it lasts 5 years and then ends at the end of year 5.

The setup for solving this problem is very similar to another post I wrote on how to calculate net present value (NPV) a while back.

Here’s how I’d set up the calculation in Excel to solve this problem, assuming the project lasted 5 years:

Read the rest of this entry »

Welp, all those interview rounds I’ve had the past several weeks paid off in the end, and I received and accepted a very nice job offer with a new company this week! I’m pretty excited and will start in just a little over a week’s time.

Not to worry, my new position is still in corporate finance, so I’ll have plenty of practice working with Excel and all the typical finance tools to create how-to posts aplenty.

Whereas my past finance positions focused mainly on scrutinizing costs related to manufacturing and operations, the new one is more focused on the revenue side of things, with a greater connection to the end-customer. The position’s scope should also be greater and offer more opportunities than did my past jobs. Always a positive.

That’s about it for now. More to come after I actually start!

One of the side perks of running this site is that I occasionally get invited to review products and services. Since I suppose I write so much about using Excel, the creator of BonaVista MicroCharts contacted me in December about his Excel add-in, and from Germany, no less! In the interest of full disclosure, I am not being paid to write this review; however, he did provide me with a professional license key to test out the product.

You might have noticed that I’ve been using it in a couple of my posts (like here and here) that include MicroCharts built using Excel 2003. What can I say? I love the application, and even if I’d stumbled upon this product on my own, I’d be inclined to get it to use at work, anyway. MicroCharts offer a slew of different types of charts (pie, line, column, bar, bullet). For such a simple application, I find them pretty powerful. Here are some example column charts (click to enlarge):

Read the rest of this entry »

I wanted to show one other thing helpful thing that Excel can do when you need to highlight data called conditional formatting. (Note: this tutorial is image heavy!)

Conditional formatting basically allows you to use colors, font styles, and borders to highlight cells that meet certain conditions. Unlike IF and other Excel functions, conditional formatting is found as a pull-down menu item in Excel rather than a function that you can type into a cell.

With the same salesman example I’ve been using in the previous tutorials on using the IF function, let’s look at how this would work. First, here’s the raw data, unformatted:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

Ok, now back to the tutorial.

Suppose you wanted to highlight all the sales that were above the minimum amount of $100K green, and any that were below the minimum amount red. How would you do that?

Read the rest of this entry »