Spreadsheets: internal rate of return (IRR) on LibreOffice Calc

The tutor promotes his equal-opportunity point of view towards computing.

I use Microsoft’s programs: witness my post from Sept 28 about using Excel to calculate IRR.

However, some of my computers aren’t Windows; rather, they’re Linux. I don’t have a Mac.

Although Windows dominates the PC landscape, I want to offer equal opportunity to those using Linux. My Linux computers come with the LibreOffice suite, which is under the Mozilla Public License. Today I’ll tell how to calculate IRR using LibreOffice Calc, which is the LibreOffice spreadsheet.

I’m no expert on spreadsheets, but I’d say LibreOffice Calc reminds me of an earlier version of Excel, or perhaps Lotus. The point: it’s not that different.

Here’s what I find the easiest way to calculate IRR for the set of cash flows

-130000, -60000, 0, 215000, -7500

on LibreOffice Calc:

  1. Open LibreOffice Calc. Enter the cash flow values across a row of cells, for instance a6 to e6.
  2. Now select another cell, maybe g6.
  3. In that cell, type =IRR(a6:e6), then press ENTER. The value 3.38% will likely appear in the cell:)

Recall the answer from Excel in my post from Sept 28: 0.033777697 or 3.3777697%. Rounded to two decimal places, it agrees with the 3.38% we are getting from LibreOffice Calc.

In Excel, the instructions above will also work.

I’ll be talking much more about Excel and LibreOffice Calc.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Probability: when to add, when to multiply

The tutor offers points about combining probabilities.

My gut reaction, thinking about when to add probabilities, is that it’s done less often than multiplying. However, there is one obvious type of situation in which you add:

Example 1

Each ticket bought has 1/1000 probability of winning a new PC. Josh buys five tickets. What is his probability of winning?

Solution:

In this case, Josh’s probability of winning is the sum of each of his tickets’ winning chances:

P(Josh wins)=1/1000+1/1000+1/1000+1/1000+1/1000=5/1000=1/200

Note that if one ticket wins, the others can’t. Such a premise – where if event X happens, then event Y cannot – is called mutual exclusivity. In turn, we refer to X and Y as mutually exclusive events. Normally, when probabilities are added, they belong to mutually exclusive events, either of which will achieve the same outcome.

Generally, probabilities are multiplied under the following conditions:

  1. The events happen in sequence, or else they both happen.
  2. The events are independent of one another; ie, if one event happens, it doesn’t affect the other’s likelihood of occurring.

Example 2

At a fishing hole, trout is caught with 55% probability; whitefish, 32%. Assuming you catch two fish, what is the probability that the first is a trout, then the second, a whitefish?

Solution:

We assume there are so many fish present that catching one does not affect the probability of which kind you’ll catch next.

P(TW)=P(T)xP(W)=(0.55)(0.32)=0.176

Next we look at a problem whose solution needs both operations:

Example 3

A husband and wife have a joint chequing account. Each cheque needs only one signature; either can sign. The cheques are all written in either black or blue. The wife prefers blue ink; 80% of the time she signs in blue. The husband prefers black; 75% of the time, he signs in black. The wife is more interested in managing the account: in total, 85% of the cheques are written by her.

What is the probability that a cheque from the couple is signed in black?

Solution:

Since only one person signs the cheque, we can add the probabilities of either doing so:

P(Black)=P(wife wrote it, signed in black)+P(husband wrote it, signed in black)

Recall that the wife writes 85% of the cheques, and signs in black only 20% of the time:

P(wife wrote the cheque and signed it in black)=0.85×0.20

If the wife writes 85% of the cheques, the husband must write the other 15% of them. He signs in black 75% of the time:

P(husband wrote the cheque and signed it in black)=0.15×0.75

Therefore,

P(Black)=P(wife wrote it, signed in black)+P(husband wrote it, signed in black)

becomes

P(Black)=0.85×0.20 + 0.15×0.75=0.17 + 0.1125=0.2825

Apparently, the probability a given cheque will be in black is 0.2825, or 28.25%.

I’ll be talking much more about probability in coming posts:)

Source:

Ross, Sheldon. A First Course in Probability. New York: Macmillan, 1988.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Spreadsheets: Excel: calculating internal rate of return (IRR)

The tutor finally brings up the elephant in the room.

I’ve been writing this blog since August 2012; herein are over 500 articles confronting problems, methods, or ideas that might occur in an academic milieu. Numerous of them centre on calculator use. Before today, however, I’ve never discussed using a spreadsheet.

My students generally don’t use spreadsheets for their math or science, except possibly for organizing lab data. Rather, like I did, they use hand-held calculators.

For the business courses I’ve taken, spreadsheets weren’t promoted; once again, hand-held calculators, such as the HP 10B, seemed to be preferred. The likely reason was that for the exam, they couldn’t provide each student a computer.

Everyday business, however, is not an exam. Only attaining the answer matters: if you can find an easier way, you’ll do well to use it. We can probably assume, in such context, that you have access to a PC with a spreadsheet program – likely Excel.

If, at your business desk, you have to solve an IRR (internal rate of return) problem, you’ll likely be drawn towards using Excel rather than a hand-held calculator.

For now, let’s exchange our lecture hall seat for an office workstation.

Back in my September 14/15 post, I describe IRR (internal rate of return) and show how to solve a sample problem using the HP 10B business calculator. The problem boils down to the following:

Find the internal rate of return for the following cash flows:
-130000, -60000, 0, 215000, -7500

Solution:

Open Excel and click a cell. Next, click the formulas tab near the top, then click Financial, which might have a green book icon. A dropdown menu will appear; scroll down to IRR, then click it.

Presently you should see a dialogue box that has IRR in the upper left corner. The cursor will likely be in an input box titled Values.

Type the cash flows list in the values box, enclosed in parentheses:

{-130000, -60000, 0, 215000, -7500}

As soon as you close the parentheses, you’ll likely see the number 0.033777697 appear in the dialogue box. After you click OK, the cell may just show 3%, but can be formatted to show more precision. (I’ll cover how to do that in another post.)

Excel hasn’t been told whether the period of each cash flow is months or years, so the answer 0.033777697 means 3.3777697% per period. In the original problem from my September 14/15 post, the cash flows happen at monthly intervals. Nominally, 3.3777697% per month leads to 40.53% per year (3.3777697%x12). Therefore, Excel gives 40.53% per year as the IRR for the given list of cash flows. That’s exactly how the HP 10B answers as well.

In future posts, I’ll no doubt be saying much more about Excel spreadsheet calculations. Furthermore, I’ll try to cover the corresponding function in a generic spreadsheet:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Math: conditional probability: a first example

The tutor wades into a case of conditional probability.

Conditional probability involves the idea that with extra knowledge of a situation, the likelihood of a given outcome can change. Consider the following premise:

Example 1

At ABC Insurance, the general likelihood of living past age 75 is 41%. What is Herbert’s likelihood of living past 75, given

a)that he is age 30?

b)that he is age 80?

Solution:

For question a), at age 30, Herbert likely still has about 41% probability of living past 75.

In the case of b), at age 80, Herbert has 100% probability of surviving past age 75: he has already done it.

The formula for the conditional probability of Event X given Event Y, P(X|Y), is

    \[P(X|Y)=\frac{P(Y and X)}{P(Y)}\]

For b) above,

    \[P(Herbert\ lives\ past\ 75|he's\ 80)=\frac{P(Herbert\ is\ 80\ and\ past\ 75)}{P(Herbert\ is\ 80)}\]

Of course, the probability Herbert is 80 and past 75 simply equals the probability he is 80: being 80, he’s inherently past 75.

Therefore,

    \[P(Herbert\ lives\ past\ 75|he's\ 80)=\frac{P(Herbert\ is\ 80)}{P(Herbert\ is\ 80)}=1=100\%\]

I’ll be giving further examples of conditional probability in future posts:)

Source:

Ross, Sheldon. A First Course in Probability. New York: Macmillan, Inc., 1988.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Math: probability: unequal likelihood and odds

The tutor imagines an experiment in probability.

For most people, probability becomes interesting when you can apply it to a believable situation. Let’s explore the following premise:

Example 1

Around a farm live twelve wild foxes. Eleven are red, but one is white. In the same field of vision, the white one is four times as likely to be noticed as a red one. Assuming the foxes all behave similarly, what is the probability that, out for a walk on the farm, you’ll notice the white one first?

Solution:

If the white fox is four times as noticeable as a red one, the ratio between the likelihood of seeing it and the eleven red ones can be given as 4:11. Some people might call this ratio the odds of seeing the white one first.

The 4:11 ratio suggests that in fifteen fox sightings, four will be of the white one. Therefore, the probability of seeing the white fox first is 415.

Notice that odds and probability, though closely related, are not the same.

I’ve never seen any fox outdoors:)

Source:

Tan, S. T. Applied Finite Mathematics. Boston: PWS-KENT, 1990.

stackoverflow.com

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

September 23: first day of fall

The tutor reflects on the beginning of what might be his favourite season.

I don’t recall the first day of fall being the 23rd. I would naively have imagined it to be the 21st, as it always seemed to be when I was a kid in school. Whether I just remember wrong or it’s actually changed, makes for delicious contemplation in quiet moments.

Sunday evening I had the fortune of watching the sun set through the kitchen window, which looks straight east. I’d say it finally sank beneath the horizon at about 7:10pm.

As I understand it, the first day of fall – and of spring – is the same length for everyone: twelve hours, theoretically. I’ve come to believe that equinox means night and day are equal: twelve hours each.

Without daylight-savings time, one might imagine a twelve-hour day running from 6am to 6pm – which is what I’m told happens in the tropics year-round (more or less). Those of us who live in the north – we’re about 50degN here – are used to long summer days and short winter ones. I’d say our summer days reach about 16 hours in late June, while in late December, the day can be as short as 8hrs 5min.

Of course, with daylight savings time, our twelve hour day runs roughly from 7am to 7pm. Sunday’s 7:10pm sunset seems to agree. I can’t watch the sun rise from the kitchen because houses and trees obscure it. However, environment canada claims that today’s sunrise was 7:08am. I’d say we’re pretty close to that twelve hour day.

Some people love the fall; others feel the opposite way, seeing it as the end of carefree days when you don’t need coat and boots to leave the house. Where I live, the climate is mild, but with fall commences our seven-month rainy season. This afternoon it’s 12C, rainy, and windy; it’s very difficult to walk from the car to the door without getting quite wet.

Let fall begin:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

English: High School Literature: Roald Dahl’s “The Landlady”

The tutor discusses a short story from a grade nine literature textbook.

Searching for reading material for my students, I recently chose “The Landlady” by Roald Dahl, from the textbook In Context: Anthology Three.

For a tutor, choosing literature that’s not only challenging, but also interesting, is critical, but potentially difficult. Students can be discriminating about reading material. Moreover, unlike a teacher, a tutor likely doesn’t have the power to assign reading. The easiest situation is when the literature appeals to the student.

Roald Dahl’s “The Landlady”, while it doesn’t contain any direct supernatural content, constantly hints at mysterious plans and stage-managing. A teenage reader can get drawn into the story by curiosity. The ending doesn’t give any resolution; the reader is left wondering – imagining – what will happen.

The story worked out very well for my student:)

Source:

Sorestand, Glen, Christine McClymont and Clayton Graves. In Context: Anthology Three.    Scarborough: Nelson Canada, 1990.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Math: geometric sequences: depreciation

The tutor brings up a real-life application for geometric sequences.

A geometric sequence is a list of numbers that keep changing by a constant ratio; for example:

3,6,12,24,48….

In the above sequence, t3=12.

A perfect fit for geometric sequences is depreciation:

Question 1:

A car originally valued at $10,000 depreciates by 18% per year. Find its value at the end of 7 years.

Solution:

The fact that it depreciates by 18% means that it retains 82% of its value annually. The constant ratio is therefore 82%, or 0.82, as shown:

10000, 10000(0.82), 10000(0.82)2, 10000(0.82)3….

Note that the first term, 10,000, doesn’t include any depreciation; the second term is the result of the first year’s depreciation. Similarly, the eighth term, which will be 10000(0.82)7, will be the value after seven years’ depreciation:

t8=10000(0.82)7=2492.85

Apparently, the car’s value after 7 years’ depreciation is $2492.85.

Source:

Travers, Kenneth J. et al. Using Advanced Algebra. Toronto: Doubleday Canada, 1977.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Neighborhood plant discoveries: Chinese lantern

The tutor shares another plant he noticed in the neighborhood.

On the way in for lunch at a pub with my wife, I pointed to a plant with big orange capsules hanging from it like Christmas ornaments. “I’ve seen those before,” I pointed, “but don’t know what they’re called.”

“Chinese lanterns,” my wife rejoined. The plants were prime specimens, obviously well tended for best effect. If you want to see what they look like, see gardeningknowhow.com.

Gardeningknowhow.com points out that Chinese lantern is of the nightshade family, along with tomatoes and peppers. Wikipedia points out that it is native across southern Eurasia. Furthermore, it produces ethyl caffeate, which is a possible compound for future use against liver disease.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Math: Arithmetic series: a typical problem

The tutor introduces a typical arithmetic series example.

An arithmetic series is a sum of numbers which change by repeated addition. An example:

5+11+17+23+….+143

The question posed might be as follows:

Find the sum of the arithmetic series 5+11+17+23+….+143.

Solution:

A formula for the sum is

    \[S_n=\frac{n(t_1+t_n)}{2}\]

where

t_1 is the first term

t_n is the last term

n is the number of terms.

Note that while, in our case, t_1=5 and t_n=143, we don’t yet know n.

However, we can use the term formula

    \[t_n=t_1+d(n-1)\]

in which d is the amount added to get the next term; in our case, d=6.

We can find n as follows:

    \[143=5+6(n-1)\]

which becomes

    \[138=6(n-1)\]

Next, dividing both sides by 6, we get

    \[23=n-1\]

    \[n=24\]

Apparently 143 is the 24th term. Now, to find the sum of 5+11+17+23+….+143, we can use

    \[S_n=\frac{n(t_1+t_n)}{2}\]

as follows:

    \[S_24=\frac{24(5+143)}{2}=\frac{24(148)}{2}=12(148)=1776\]

Apparently, 5+11+17+23+….+143=1776:)

Source:

Travers, Kenneth J. et al. Using Advanced Algebra. Toronto: Doubleday Canada, 1977.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.