Pfe Chap02
Pfe Chap02
Pfe Chap02
Overview..............................................................................................................................2
2.1. Different forms of financial organization ....................................................................3
2.2. Personal income taxes in the United States ...............................................................10
2.3. Corporate taxation in the United States .....................................................................15
2.4. Whats betterbeing a corporation or a sole proprietorship?...................................16
Conclusion .........................................................................................................................23
Exercises ............................................................................................................................24
Appendix: Three Excel functions which can simplify tax computations .........................27
This is a preliminary draft of a chapter of Principles of Finance with Excel. 2001 2005 Simon Benninga
(benninga@wharton.upenn.edu ).
page 1
Overview
This chapter discusses two inter-related topics which form the background for the rest of
the book: The forms of business organization and the taxation of business income.
Business forms: The simplest form of a business is the sole proprietorship, a business
that is owned by a single person. More complicated forms of business organization are
partnerships, corporations, or limited liability companies.
Business taxation: Taxes are a fact of life. In this book you will learn how to integrate
taxes into optimal financial decision making. As you will see, taxes affect the paybacks
you can expect from assets and hence affect optimal financial decision making. One of
the primary ways in which the organizational form affects a business is in the taxation of
the business income. Sections 2.2 and 2.3 discuss income taxation in the United States.
These sections will show you how income is taxed at both the personal and the corporate
level.
To incorporate or not? Section 2.4 of this chapter compares the taxation of a sole
proprietorship/partnership/limited liability company with the taxation of a corporation.
Sole proprietorship
Partnership
Corporation
Shareholders
page 2
Taxation
Sum
If
Sole proprietorship
A sole proprietorship is a business owned by a single person. It is the simplest form of
business organization. From the organizational point of view, a sole proprietorship requires no
paperworkyou simply start your business and thats it.
The income and expenses of a sole proprietorship are reported by the owner on her or his
own personal income tax return. This means that there is no legal separation between the
business and the owner. This lack of legal separation differentiates sole proprietorships from
corporations. It has its good points (simplicity, primarily), but also its bad points. The biggest
disadvantage of the lack of legal separation is that the liabilities of the sole proprietorship are the
page 3
personal liabilities of its owner. In principle all of the sole proprietors assets (both personal and
business-related) can be used to pay off the businesss debts.1
Partnership
A partnership is a business owned by two or more people. Usually the partners split the
management and the profits of the business, though there are many exceptions to this rule.
Partnerships are almost as easy to start as sole proprietorships:
paperwork (though many partners wisely choose to record the formalities of ownership,
management and profit sharing).
partnerships: Although they may choose to elect officers, hold meetings, and make formal
records of business relationships, partnerships are not required by law to do so.
From the tax point of view, the income of a partnership is reported by each of the partners
on their own personal income tax returns.
partnership income is passed through to the partners. As in the case of a sole proprietorship,
the personal and business-related assets of the partners can be used to pay off the partnership
debts.
Corporation
A corporation is a separate legal entity established for doing business. From a legal
point of view, the corporation is a separate legal person. This means that corporations can
The general rule that in a sole proprietorship there is no distinction between personal and business assets has many
exceptions.
homestead exemption) and personal property up to a certain amount is also exempt (the wildcard exemption).
page 4
make contractual arrangements, borrow money, sell shares, and buy other businesses.
Corporations also pay taxes. Whereas neither sole proprietorships nor partnerships can be sued
(you can sue the owners, but not the business, since it has no separate legal standing),
corporations can be sued. On the other hand, the legal liabilities of the corporation do not, in
general, extend to become the liabilities of the corporations owners.
Whereas the organization of a sole proprietorship or a partnership is quite simple, the
organization of a corporation requires some legal formalities. Corporations are separate legal
entities that must be registered in a particular state. The owners of a corporation are called its
shareholders.
The limited liability of a corporation generally means that only the corporations assets
can be claimed in payment for a corporate debt and not the personal assets of the corporations
owners (unless that corporate debt has been personally guaranteed by an owner or employee of
the corporation).
Generally, this limited liability covers all judgments entered against the
corporation, as long as the owners and/or employees involved were acting in their corporate
capacity and without the intention to defraud. Limited liability is the primary reason why
corporate structure is so widespread.
dividends, the shareholders must pay taxes on their dividends at their personal income tax rates.
Heres an example: The Garden family owns Brass Tacks, Inc., a business that makes
picture frames. Estelle Garden and her daughter Terry are the only shareholders of Brass Tacks.
page 5
Estelle owns 90% of the shares of Brass Tacks and her daughter Terry owns the other 10% of the
shares.
In 2006 Brass Tacks had a profit before taxes of $1 million. The company pays a
corporate income tax of 35% on this income, leaving it with $650,000 of after-corporate-tax
income. Of this income, the company decided to pay a dividend of $400,000 to its shareholders.
Since Estelle owns 90% of the shares, she got $360,000 of this dividend; Terry got the other
$40,000.
The dividend income received by Estelle and Terry is taxable as personal income. If
Estelles tax rate is 40%, she will end up with $216,000 of after-personal-tax income from the
dividend ( $216,000 = (1-40%)*$360,000 ) . If Terrys personal income tax rate is 25%, she will
have $30,000 of after-personal-tax income from the dividend ( $30,000 = (1-25%)*$40,000 ) .
Theres one other thing to notice about this example: Each of the ownersEstelle and
Terryis taxed twice on her income from the corporation. In section 2.3 we return to this
double taxation of corporate income.
Corporations and LLCs generally do not pay federal income tax, but instead pass through their
income to the corporations owners, resulting in one level of taxation. Note that LLCs are
page 6
usually more flexible than S corporations, as the latters many restrictions result in compliance
difficulties.
page 7
Sole proprietorship
Partnerships
page 8
Corporationss
A corporation is a
separate legal entity,
whose income is
separately taxed.
Disadvantages
Complicated to run and
organize. Two levels of
income taxation
corporate and personal.
Profits which are passed
through to the
corporationss owners
are taxed at the owners
personal tax rates.
Good websites:
http://www.nolo.com/lawcenter/ency/article.cfm/ObjectID/B6061AF8-E1FE-43D9B3117C83BD1CCA82/catID/B491956E-A152-424B-A2342A5861B5EACF
Limited
Liability Expenses recognized as Advantages?
business costs?
Company (LLC)
Disadvantages
Limited liability.
No double taxation of
income. LLC income is
passed through to the
owners and taxed at the
owners personal tax
rates.
Good websites:
http://www.nolo.com/lawcenter/ency/article.cfm/ObjectID/ED01121A-B4BF-498A8BC0DBD121A0C869/catID/BAAE1B67-F54A-41B4-91943A51F56C3F79
page 9
Fifteen percent tax on income between $7,150 and $29,050. This works out to $3,285 =
15% * ($29,050 - $7,150).
Twenty-five percent tax on income over $29,050. As you can see on the tax table, this
income is taxed at a 25% rate. This works out to $738 = 25% * ($32,000 - $29,050) .
page 10
But not
over
$7,150
$29,050
$70,350
$146,750
$319,100
Tax rate
10%
15%
25%
28%
33%
35%
On amount
over
$0
$7,150
$29,050
$70,350
$146,750
$319,100
Figure 2.2. U.S. Federal income tax table for a single taxpayer.
This tax calculation can be easily done in Excel, as illustrated in cells B14:B17 below:
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
7,150.00
29,050.00
70,350.00
146,750.00
319,100.00
Your income
32,000
Tax computation
Tax on first $7,150
Tax on income to $29,050
Tax on income over $29,050
Total tax
$
$
$
$
715
3,285
738
4,738
Tax rate on
bracket
10%
15%
25%
28%
33%
35%
<-- =10%*7150
<-- =15%*(29050-7150)
<-- =25%*(B11-29050)
<-- =B14+B15+B16
page 11
The marginal tax rate is the rate paid on the last dollar of income earned; in this example the
marginal tax rate is 25%, the rate that applies to income between $29,050 and $70,350.
page 12
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$
$
$
$
$
$
$
32,000
1,428
896
2,324
29,676
$
$
$
$
$
$
<-- =4.4625%*B2
<-- =2.8%*B2
<-- =B4+B3
<-- =B2-B5
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$
$
$
$
$
100,000
4,463
2,800
7,263
92,738
$
$
$
$
$
$
715
3,285
10,325
6,269
-
$
$
<-- =4.4625%*B2
<-- =2.8%*B2
<-- =B4+B3
<-- =B2-B5
<-- =10%*7150
<-- =15%*(29050-7150)
<-- =25%*(70350-29050)
<-- =28%*(B6-70350)
page 13
A single taxpayer is an individual who pays taxes only on his or her own income.
A married individual filing jointly reports the income of herself and her spouse on the
same income tax form.
A married individual filing separately reports only her own income. Her spouse
reports his income separately. The tax rates which apply to a married individual filing
separately are the same as those which apply to a single taxpayer.
A head of household is a taxpayer who supports some other individual. This individual
could be a child, a parent, a spouse, or a non-relative who permanently resides in the
household.
As you can see in Figure 2.3, income tax rates depend on the filing status of the
individual.
page 14
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Over
$
$
$
$
$
$
7,150.00
29,050.00
70,350.00
146,750.00
319,100.00
Income
Tax computation
Tax on first $7,150
Tax on income to $29,050
Income over $29,050
Tax on income over $29,050
Total tax
Tax rate on
current bracket
10%
15%
25%
28%
33%
35%
715
3,285
2,950
738
4,738
<-- =C5*B5
<-- =C6*(B6-B5)
<-- =B12-B6
<-- =C7*B16
<-- =B14+B15+B17
14,300
58,100
117,250
178,650
319,100
Income
Tax computation
Tax on first $14,300
Tax on income over $14,300
Total tax
Tax rate on
current bracket
But not over
$
14,300
10%
$
58,100
15%
$
117,250
25%
$
178,650
28%
$
319,100
33%
35%
$32,000
$
$
$
36
37
$32,000
$
$
$
$
$
Single Taxpayer
Taxable income
2
3
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
Over
$
$
$
$
$
$
56
57
58
59
60
61
62
63
64
65
66
67
68
Over
$
$
$
$
$
$
7,150
29,050
58,625
89,325
159,550
Income
Tax computation
Tax on first $7,150
Tax on income to $29,050
Income over $29,050
Tax on income over $29,050
Total tax
Tax rate on
current bracket
But not over
$
7,150
10%
$
29,050
15%
$
58,625
25%
$
89,325
28%
$
159,550
33%
35%
$32,000
$
$
$
$
$
715
3,285
2,950
738
4,738
<-- =C39*B39
<-- =C40*(B40-B39)
<-- =B46-B40
<-- =C41*B50
<-- =B48+B49+B51
Heads of Households
Taxable income
10,200
38,900
100,500
162,700
319,100
Income
Tax computation
Tax on first $10,200
Tax on income over $10,200
Total tax
Tax rate on
current bracket
But not over
$
10,200
10%
$
38,900
15%
$
100,500
25%
$
162,700
28%
$
319,100
33%
35%
$32,000
$
$
$
Figure 2.3: Tax tables for 4 kinds of filers in United States, showing taxes payable on $32,000
of annual income. Single taxpayers and married individuals filing separately pay the most tax;
married individuals filing jointly pay the least taxes.
page 15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
50,000
75,000
100,000
335,000
10,000,000
15,000,000
18,333,333
Corporate income
Tax on first $50,000 (15%)
Tax on income over $50,000 less than $75,000 (25%)
Tax on income over $75,000 less than $100,000 (34%)
Tax on income over $100,000 less than $335,000 (39%)
Tax on income over $335,000 less than $10,000,000 (34%)
Tax on income over $10,000,000 less than $15,000,000 (35%)
Tax on income over $15,000,000 less than $18,333,333 (38%)
Tax on income over $18,333,333 (35%)
U.S. corporate income tax
Not over
$
50,000
$
75,000
$
100,000
$
335,000
$ 10,000,000
$ 15,000,000
$ 18,333,333
$
$
$
$
$
$
$
$
$
$
500,000
7,500
6,250
8,500
91,650
56,100
170,000
Tax rate
15%
25%
34%
39%
34%
35%
38%
35%
<-- =15%*50000
<-- =25%*(75000-50000)
<-- =34%*(100000-75000)
<-- =39%*(335000-100000)
<-- =34%*(500000-335000)
A corporation with $500,000 of annual income (cell B12) pays Federal income taxes of
$170,000. The companys average tax rate is 34% =
170,000
(cell B23).
500,000
The corporate tax rate schedule is designed so that companies with income over
$18,333,333 pay an average tax rate of 35%.
In an attempt to be gender inclusive, he and she are used throughout Principles of Finance with Excel in
page 16
compares it to the taxation of a sole proprietorship, whose income is taxed only at the personal
level.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
7,150.00
29,050.00
70,350.00
146,750.00
319,100.00
Tax rate on
bracket
10%
15%
25%
28%
33%
35%
Your income
500,000
Tax computation
Tax on first $7,150 (10%)
Tax on income to $29,050 (15%)
Tax on income to $70,350 (25%)
Tax on income to $146,750 (28%)
Tax on income to $319,100 (33%)
Tax on income over $319,100 (35%)
Total tax
$
$
$
$
$
$
$
715
3,285
10,325
21,392
56,876
63,315
155,908
<-- =10%*7150
<-- =15%*(29050-7150)
<-- =25%*(70350-29050)
<-- =28%*(146750-70350)
<-- =33%*(319100-146750)
<-- =35%*(B11-319100)
<-- =SUM(B14:B19)
Jennifers brother Rob Smith has the same kind of business, but chose to incorporate.
Robs corporation is called RobSmith, Inc. This means that RobSmith, Inc. first has to pay
corporate income taxes of $170,000 on the $500,000 income (cell B22 below). This leaves
RobSmith, Inc. with $330,000 of after-tax income (B24), which it then pays as a dividend to Rob
PFE, Chapter 2: Business organization and taxes
page 17
Smith. This dividend is liable to Federal personal income taxes. When all is said and done, Rob
will have $233,593 as after-tax income (cell B49 below).
page 18
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
50,000
75,000
100,000
335,000
10,000,000
15,000,000
18,333,333
Not over
$
50,000
$
75,000
$
100,000
$
335,000
$ 10,000,000
$ 15,000,000
$ 18,333,333
Tax rate
15%
25%
34%
39%
34%
35%
38%
35%
Corporate income
$ 500,000
$
$
$
$
$
$
$
$
$
7,500
6,250
8,500
91,650
56,100
170,000
27
28
Personal tax
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
Over
$
$
$
$
$
$
<-- =15%*50000
<-- =25%*(75000-50000)
<-- =34%*(100000-75000)
<-- =39%*(335000-100000)
<-- =34%*(B13-335000)
<-- =SUM(B14:B21)
Taxable income
7,150.00
29,050.00
70,350.00
146,750.00
319,100.00
Tax rate on
bracket
10%
15%
25%
28%
33%
35%
Your income
Tax computation
Tax on first $7,150 (10%)
Tax on income to $29,050 (15%)
Tax on income to $70,350 (25%)
Tax on income to $146,750 (28%)
Tax on income to $319,100 (33%)
Tax on income over $319,100 (35%)
Total tax
$
$
$
$
$
$
$
715
3,285
10,325
21,392
56,876
3,815
96,408
<-- =10%*7150
<-- =15%*(29050-7150)
<-- =25%*(70350-29050)
<-- =28%*(146750-70350)
<-- =33%*(319100-146750)
<-- =35%*(B37-319100)
<-- =SUM(B40:B45)
page 19
Corporations can take more expenses for tax purposes than individuals. For example, it
may be that Robs corporation can buy him a car and legally report the purchase of the
car as an expense. If Rob has employees, it may be that his corporation can pay for their
child-care expenses and pensions and deduct these payments as an expense. Figure 2.3
shows a large range of corporate expenses which the state of Arizona allows its
corporations.
proprietorships.
Corporations can have shareholders. If Rob wants his company to grow and wants to
have many people invest in RobSmith Inc., he will need to become a corporation and
issue shares. Jennifers sole proprietorship is not legally able to sell shares.
corporation is worth, the litigants will not have access to Robs personal propertyhis
house, his car, his investments. On the other hand, if Jennifer gets sued, all her personal
property is fair game.
Corporations have a choice about what to do with their income. RobSmith Inc. can
choose to pay out some of its income as a salary to Rob, and can choose to retain the rest
page 20
of the income in the corporation to finance future growth. Only that part of the income
paid out to Rob is subject to double taxation. Jennifer has no such choice.
page 21
Figure 2.3. Why incorporate? Here is a list of corporate expenses which are valid under the
Arizona tax code. These tax breaks are available only to corporations. Despite the double
taxation of corporate income, it may be worthwhile for a company to incorporate.
http://www.gpec.org/InfoCenter/Topics/Incentives/CorporateIncomeTaxIncentives&Credits.html
page 22
Conclusion
Taxes are extremely important. This chapter relates the taxes of businesses to the form of
business organization. At this point you should have a better concept of how the income tax
system works to tax both personal and corporate incomes.
page 23
Exercises
1. John Doe leaves in Anycity, Anystate, USA. Mr. Doe works as a vice president in a hightech firm and his annual salary is $125,000. Federal income tax in Anystate is based on the tax
schedules given in Section 2.2. The state income taxes in Anystate depend on the tax bracket:
On income between $0-$15,000, the Anystate tax is 4%
From $15,001 to $30,000, the Anystate tax rate is 6%
Above $30,000, the rate is 8%.
Additionally Mr. Doe has to pay 3.2% for the municipality.
Assuming that Mr. Doe is single, compute his federal, state and local taxes. What will be his net
income?
2. Mr. Doe just turned 55 this year. In Anystate residents between the ages of 55-65 can declare
$20,000 saving for pension without paying income taxes on them. However, the remainder of
the income is fully taxable. Compute Mr. Does taxes, assuming that he saves the $20,000.
What is the tax advantage of his being over 55?
3. In this chapter the tax code for both Nevada and Philadelphia, Pennsylvania is specified.
Calculate the difference in tax payment between Nevada and Philadelphia for different earning
levels (at least three).
4. The ABC corporation had last year total profit of $25,000,000. Calculate the tax payment
of the company, according to the Federal corporate tax brackets in Section 2.3.
page 24
5.
payments, assuming no state or local taxes. Compare these payments to the Federal corporat tax
payment you computed in the previous question. How do you explain the fact that the tax
payments are similar although the tax brackets are different?
6.
After finishing your Ph.D. in finance you decided to open a consulting business for
companies in Nevada. According to your estimation your annual income will be $200,000 in the
first few years. You are considering two options being a corporation or a sole proprietorship.
Your accountant estimated that being a corporation will able you to register an additional
$30,000 as expenses. Calculate in which alternative you will pay less tax.
7. Janice Jane Johnson (JJJ) is a famous writer living in Nevada. Each six years JJJ finishes a
new novel, which she then sells to her publisher for the sum of $750,000. Until now JJJ was a
sole proprietorships, but her accountant has advised her to become a corporation.
The
JJJ can smooth her income over the six years cycle (meaning $125,000
annually) thus enjoying lower tax brackets.
In which alternative JJJ will pay fewer total taxes over the six year span?
8. Go back to Estelle and Terry, the owners of Brass Tacks (section 2.1). Estelle owns 90% of
the shares, and Terry owns the rest. Brass Tacks has a tax rate of 35%, Estelle has a tax rate of
40% on her dividend income, and Terry has a tax rate of 25%. Suppose Brass Tacks pays out all
page 25
its after-tax profits as dividends. What are the total taxes (corporate + personal) that Estelle and
Terry pay on their dividend? What is the total tax rate of each?
page 26
Sum
The Excel function Sum adds a series of numbers. We have used this function to add a
series of numbers, as in the example below:
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
7,150.00
29,050.00
70,350.00
146,750.00
319,100.00
Tax rate on
bracket
10%
15%
25%
28%
33%
35%
Your income
32,000
Tax computation
Tax on first $7,150
Tax on income to $29,050
Tax on income over $29,050
Total tax
$
$
$
$
715
3,285
738
4,738
<-- =10%*7150
<-- =15%*(29050-7150)
<-- =25%*(B11-29050)
<-- =SUM(B14:B16)
In Cell B17 we have used the Sum function instead of the simple addition
=B14+B15+B16.
PFE, Chapter 2: Business organization and taxes
page 27
If
The Excel function If allows you to condition your answer in a particular cell based on
other information.
Heres an example: WeNeverCrash Airlines is running a youth special on airfares
from St. Louis to Chicago. If youre under 23, you can get a standby fare for $75, whereas the
regular fare on the route is $200. In the spreadsheet below we use the If function to compute
your fare:
A
1
2 Your age
3 Your airfare from St. Louis to Chicago
25
200 <-- =IF(B2<23,75,200)
1
2 Your age
3 Your airfare from St. Louis to Chicago
4
5
6 =IF(B2<23,75,200)
7
8
Is your age (cell B2) less than 23?
9
10
Age < 23, airfare = 75
11
Age > 23, airfare = 200
12
25
200 <-- =IF(B2<23,75,200)
page 28
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Taxable income
Over
$
$
$
$
$
$
7,150.00
29,050.00
70,350.00
146,750.00
319,100.00
Tax rate on
bracket
10%
15%
25%
28%
33%
35%
Your income
32,000
Tax computation
Tax on income to $7,150
Tax on income between $7,150 and $29,050
Tax on income between $29,050 and $70,350
Tax on income between $70,350 and $146,750
Tax on income between $146,750 and $319,100
Tax on income over $319,100
Total tax
$
$
$
$
$
$
$
715
3,285
738
4,738
<-- =IF($B$11<A4,0,IF($B$11>B4,(B4-A4)*C4,($B$11-A4)*C4))
<-- =IF($B$11<A5,0,IF($B$11>B5,(B5-A5)*C5,($B$11-A5)*C5))
<-- =IF($B$11<A6,0,IF($B$11>B6,(B6-A6)*C6,($B$11-A6)*C6))
<-- =IF($B$11<A7,0,IF($B$11>B7,(B7-A7)*C7,($B$11-A7)*C7))
<-- =IF($B$11<A8,0,IF($B$11>B8,(B8-A8)*C8,($B$11-A8)*C8))
<-- =IF($B$11<A9,0,($B$11-A9)*C9)
The advantage of this spreadsheet is that it is completely automatedif you put in the
income in cell B11, the spreadsheet correctly computes the income tax in cell B20. For example,
if taxable income is $100,000, then U.S. Federal taxes are $22,627:
page 29
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Taxable income
Over
$
$
$
$
$
$
7,150.00
29,050.00
70,350.00
146,750.00
319,100.00
Tax rate on
bracket
10%
15%
25%
28%
33%
35%
Your income
100,000
Tax computation
Tax on income to $7,150
Tax on income between $7,150 and $29,050
Tax on income between $29,050 and $70,350
Tax on income between $70,350 and $146,750
Tax on income between $146,750 and $319,100
Tax on income over $319,100
Total tax
$
$
$
$
$
$
$
715
3,285
10,325
8,302
22,627
<-- =IF($B$11<A4,0,IF($B$11>B4,(B4-A4)*C4,($B$11-A4)*C4))
<-- =IF($B$11<A5,0,IF($B$11>B5,(B5-A5)*C5,($B$11-A5)*C5))
<-- =IF($B$11<A6,0,IF($B$11>B6,(B6-A6)*C6,($B$11-A6)*C6))
<-- =IF($B$11<A7,0,IF($B$11>B7,(B7-A7)*C7,($B$11-A7)*C7))
<-- =IF($B$11<A8,0,IF($B$11>B8,(B8-A8)*C8,($B$11-A8)*C8))
<-- =IF($B$11<A9,0,($B$11-A9)*C9)
Each tax bracket has a lower limit (in column A) and an upper limit (column B). For
example, the first tax bracket has lower limit $0 (cell A4) and upper limit $7,150 (cell
B4).
The first If statement asks whether the income is less than the brackets lower limit. If
the answer is yes, than the tax for this bracket is zero.
If the answer to the first If statement is no, then there is a second If statement. This
statement asks whether the income is greater than the brackets upper limit.
o If the answer to this question is yes, then the tax on the bracket is the bracket
size times the bracket tax rate. For example, if income (cell $B$11) is greater
than $7,150 (cell B4), then the tax on the bracket is 10%*(7150-0)=C4*(B4-A4).
page 30
o If the answer to this question is no, then the tax on the bracket is the amount of
income in the bracket times the bracket tax rate. For example, if income (cell
$B$11) is less than $7,150 (cell B4), then the tax on the bracket is
10%*(income 0) = C4*($B$11-A4).
Yes
No
Second If statement
Is income (cell B11) greater
than the column B cutoff?
Figure A.1.
Yes
No
statements.
page 31
A
1
2
3
4
5
6
7
8
9
10
11
12
$
$
Tax
Tax rate on
current
bracket
10%
15%
25%
28%
33%
35%
Tax on
previous
brackets
$
$
715.00 <-- =C4*A5
$ 4,000.00 <-- =D5+C5*(A6-A5)
$ 14,325.00 <-- =D6+C6*(A7-A6)
$ 35,717.00
$ 92,592.50
32,000
4,738 <-- =VLOOKUP(B11,A4:D9,4)+VLOOKUP(B11,A4:C9,3)*(B11-VLOOKUP(B11,A4:C9,1))
The advantage of using VLookup is that it enables you to do a concise calculation of the
taxes. Inserting any number into cell B11 above will produce a correct tax calculation in cell
B12. suppose, for example, that your income were $175,000:
A
1
2
3
4
5
6
7
8
9
10
11
12
$
$
Tax
Tax rate on
current
bracket
10%
15%
25%
28%
33%
35%
Tax on
previous
brackets
$
$
715.00 <-- =C4*A5
$ 4,000.00 <-- =D5+C5*(A6-A5)
$ 14,325.00 <-- =D6+C6*(A7-A6)
$ 35,717.00
$ 92,592.50
175,000
45,040 <-- =VLOOKUP(B11,A4:D9,4)+VLOOKUP(B11,A4:C9,3)*(B11-VLOOKUP(B11,A4:C9,1))
page 32