Question

Hello, can I please get a step by step solution using excel? Thank you!

National Business Machine Co. (NBM) has $4.4 million of extra cash after taxes have been paid. NBM has two choices to make use of this cash. One alternative is to invest the cash in financial assets. The resulting investment income will be paid out as a special dividend at the end of three years. In this case, the firm can invest in either Treasury bills yielding 2.2 percent or a 4.6 percent preferred stock. IRS regulations allow the company to exclude from taxable income 50 percent of the dividends received from investing in another company's stock. Another alternative is to pay out the cash now as dividends. This would allow the shareholders to invest on their own in Treasury bills with the same yield or in preferred stock. The corporate tax rate is 24 percent. Assume the investor has a 38 percent personal income tax rate, which is applied to interest income and preferred stock dividends. The personal dividend tax rate is 10 percent on common stock dividends. Suppose the company reinvests the $4.4 million and pays a dividend in three years. a-1. What is the total aftertax cash flow to shareholders if the company invests in T-bills? (Do not round intermediate calculations and enter your answer in dollars, not millions of dollars, rounded to 2 decimal places, e.g., 1,234,567.89.) a-2. What is the total aftertax cash flow to shareholders if the company invests in preferred stock? (Do not round intermediate calculations and enter your answer in dollars, not millions of dollars, rounded to 2 decimal places, e.g., 1,234,567.89.) a-1. Value in three years a-2. Value in three years Suppose instead that the company pays a $4.4 million dividend now and the shareholder reinvests the dividend for three years. b-1. What is the total aftertax cash flow to shareholders if the shareholder invests in T-bills? (Do not round intermediate calculations and enter your answer in dollars, not millions of dollars, rounded to 2 decimal places, e.g., 1,234,567.89.) b-2. What is the total aftertax cash flow to shareholders if the shareholder invests in preferred stock? (Do not round intermediate calculations and enter your answer in dollars, not millions of dollars, rounded to 2 decimal places, e.g., 1,234,567.89.) b-1. Value in three years b-2. Value in three years

Answer 1

SEE THE IMAGE. ANY DOUBTS, FEEL FREE TO ASK. THUMBS UP PLEASE

THIS IS THE FIRST TIME THAT SOMEONE HAS ASKED SUM TO BE DONE IN EXCEL. I HAVE PROVIDED ALL CELL REFERENCES. HAPPY TO HELP YOU

projects SIMPLE PV FV - Microsoft Excel (Product Activation Failed) View Add-Ins Formulas Data Review - 2x 11 A = = = V General A A Σ AutoSum : A Fill 2 Sort & Wrap Text Merge & Center - File Home Insert Page Layout % Cut Calibri Ea Copy Paste Format Painter BIU - D Clipboard Font A1 - Fax А m Find & $ % , :.: Insert Delete Format Conditional Format Cell Formatting as Table Styles Styles 2 Clear Filter Editing Select Alignment Number Cells C D F G H I 1 mino EXTRA CASH AVAILABLE T BILL RATE PREFERRED STOCK NO OF YEARS OF INVESTMENT 4400000 2.2% 4.6% TAX FREE DIVIDEND (COMPANIES) 50% [DIVIDEND EXCLUSION APPLIED] 11 CORPORATE TAX PERSONAL TAX PERSONAL TAX ON DIVIDEND 24% 38% 10% 12 13 14 15 a-1 16 17 18 19 a-2 N Rea INVESTMENT IN T BILL AFTER TAX YIELD ON T BILL FUTURE VALUE AFTER 3 YEARS AFTER TAX CASHFLOW TO SHAREHOLDERS INVESTMENT IN PREFERRED STOCK DIVIDEND ON PREFERRED STOCK PBP SIMPLE PV_MIRR NPV IRR REPLACEMENTS HPR GMAM ferences 1.672% (=C4*(1-C10)] 4624414.74 [=C3*(1+C15)^C6] 4161973.26 (=C16*(1-C12)] 202400.00 [=C3*C5] EAC MACRS KE KE PVPV FV EXCEL BOND I EPS EBIT 0 REPLACEMENT ROSS BUSINESS MACHINE bond si < BO. 130% DOER 9 * - O ENG 01-12-2019)

- 2x % Cut B projects SIMPLE PV FV - Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Add-Ins Σ AutoSum : A Calibri 11 A A = = = V Wrap Text General E Copy Fill 2 m Paste BIU - D A Merge & Center - $ Format Painter % , Conditional Format :.: Cell Insert Delete Format Sort & Find & Formatting as Table Styles 2 Clear Filter Select Clipboard Font Alignment Number Styles Cells Editing A31 А C D Ε F G H I 13 14 INVESTMENT IN T BILL 15 a-1 AFTER TAX YIELD ON T BILL 1.672% (=C4*(1-C10)] 16 FUTURE VALUE AFTER 3 YEARS 4624414.74 (=C3*(1+C15)^C6] 17 AFTER TAX CASHFLOW TO SHAREHOLDERS 4161973.26 (=C16*(1-C12)] 18 INVESTMENT IN PREFERRED STOCK 19 a-2 DIVIDEND ON PREFERRED STOCK 202400.00 [=C3*C5] 20 EXEMPTED DIVIDEND 101200.00 [=C19*C8] 21 NET TAXABLE DIVIDEND 101200.00 (=C19-C20] 22 TAX ON PREFERRED DIVIDEND 24288 (=C21*C10] 23 AFTER TAX PREFERRED DIVIDEND 178112.00 [=C19-C22] AFTER TAX DIVIDEND YIELD 4.048% (=C23/C3] 25 FUTURE VALUE AFTER 3 YEARS 4956257.78 [=C3*(1+C24) C6] 26 AFTER TAX CASHFLOW TO SHAREHOLDERS 4460632.00 (=C25*(1-C12)] 27 NOW IF SHAREHOLDERS RECEIVE AND INVEST 28 29 INVESTMENT IN T BILL 30 AFTER TAX DIVIDEND 3960000 [=C3*(1-C12)] 31 AFTER TAX YIELD ONT BILL 1.3640% (=C4*(1-C11)] N PBP SIMPLE PVMIRR NPV IRR REPLACEMENT S HPR GMAM EAC MACRS KE KE PVPV FV EXCEL BONDEPS EBIT REPLACEMENT ROSS BUSINESS MACHINE bond so Rea ferences BO. 130% A 9 . * 24 K I - NG ENG O 02:47 01-12-2019

- 2x Σ AutoSum : A Fill 2 Sort & m Find & Insert Delete Format 2 Clear Filter Editing Select Cells G H I 28 29 projects SIMPLE PV FV - Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Add-Ins % Cut Calibri -11AA = = = Dr Wrap Text General Ea Copy Paste BIU - D A Format Painter Merge & Center - $ % , Conditional Format :.: Cell Formatting as Table Styles Clipboard Font Alignment Number Styles B41 А C D F 27 NOW IF SHAREHOLDERS RECEIVE AND INVEST INVESTMENT IN T BILL 30 AFTER TAX DIVIDEND 3960000 (=C3*(1-C12)] 31 AFTER TAX YIELD ON T BILL 1.3640% (=C4*(1-C11)] 32 b-1 FUTURE VALUE AFTER 3 YEARS 4124263.52 (=C30*(1+C31)^C6] 33 34 INVESTMENT IN PREFERRED STOCK DIVIDEND ON PREFERRED STOCK 182160.00 [PC30*(C5)] 36 AFTER TAX DIVIDEND INCOME 112939.20 [=C35*(1-C11)] AFTER TAX DIVIDEND YIELD 2.8520% (=C36/C30] 38 b-1 FUTURE VALUE AFTER 3 YEARS 4308572.54 (=C30*(1+C37)^C6] 35 37 39 40 41 42 43 44 45 H P BP REPLACEMENTS HPR GMAM EAC MACRS KE KE PVPV FV EXCEL BOND EPS EBIT > SIMPLE PVMIRR NPV IRR ferences Red - O REPLACEMENT ROSS BUSINESS MACHINE bond si < BO. 130% 2. 9 * * ENG 01-12-2019