Chapter 5, Problem 25P

(a):

To determine

Compare the projects based on the present worth.

Explanation of Solution

Plan A: The initial cost (C) is $1,000,000 per year.

Plan B: It is a two-year contract. The cost (C) is $600,000 per semiannual starts with immediate. No payment required in the second year.

Plan C: It is a three-year contract. The cost (C) is $1,500,000 now and the second payment (C1) is $500,000 after two years.

To compare the projects, all projects’ life time should be equal. Thus, all the projects’ cash flow converts to 6 years.

Interest rate (i) is 6% per year that is compounded semiannually. Interest calculated time period (Ci) is 2. The effective interest rate per year (Ei) can be calculated as follows:

$\begin{array}{c}\text{Ei}={\left(1+\frac{\text{i}}{\text{Ci}}\right)}^{\text{Ci}}-1\\ ={\left(1+\frac{\text{0}\text{.06}}{\text{2}}\right)}^{\text{2}}-1\\ =1.0609-1\\ =0.0609\end{array}$

The effective interest rate is 6.09%.

Plan A cash flow is each year pays $1,000,000. Since the payment is made at the beginning of each year, the time period (n) is five. The present worth of plan A (PWA) can be calculated as follows:

$\begin{array}{c}\text{PWA}=-\text{C}-\text{C}\left(\frac{{\left(\text{1}+\text{Ei}\right)}^{\text{n}}-1}{\text{Ei}{\left(\text{1}+\text{Ei}\right)}^{\text{n}}}\right)\\ =-1,000,000-1,000,000\left(\frac{{\left(\text{1}+\text{0}\text{.0609}\right)}^{\text{5}}-1}{0.0609{\left(\text{1}+\text{0}\text{.0609}\right)}^{\text{5}}}\right)\\ =-1,000,000-1,000,000\left(\frac{1.343916-1}{0.0609\left(1.343916\right)}\right)\\ =-1,000,000-1,000,000\left(\frac{0.343916}{0.081844}\right)\\ =-1,000,000-1,000,000\left(4.202092\right)\\ =-1,000,000-4,202,092\\ =-5,202,092\end{array}$

The present worth of the plan A is -$5,202,092.

Plan B cash flow is each semiannual pays $600,000. Since the payment is made at the beginning of each semiannual for the first year, the time period (n1) is two, time period 2 (n2) is three, and time period 3 (n3) is seven. Since the interest is compounded semiannually, the semiannual interest (i1) is 3%$\left(\frac{6}{2}\right)$. The present worth of plan B (PWB) can be calculated as follows:

$\text{PWB}=-\text{C}\left(\begin{array}{l}1+\left(\frac{{\left(1+\text{i1}\right)}^{\text{n1}}-1}{\text{i1}{\left(1+\text{i1}\right)}^{\text{n1}}}\right)+\frac{\left(\frac{{\left(1+\text{i1}\right)}^{\text{n2}}-1}{\text{i1}{\left(1+\text{i1}\right)}^{\text{n2}}}\right)}{{\left(1+\text{i1}\right)}^{\text{n2}}}\\ +\frac{\left(\frac{{\left(1+\text{i1}\right)}^{\text{n3}}-1}{\text{i1}{\left(1+\text{i1}\right)}^{\text{n3}}}\right)}{{\left(1+\text{i1}\right)}^{\text{n3}}}\end{array}\right)$

$\begin{array}{c}=-\text{600,000}\left(\begin{array}{l}1+\left(\frac{{\left(1+\text{0}\text{.03}\right)}^{\text{2}}-1}{\text{0}\text{.03}{\left(1+\text{0}\text{.03}\right)}^{\text{2}}}\right)+\frac{\left(\frac{{\left(1+\text{0}\text{.03}\right)}^{\text{3}}-1}{\text{0}\text{.03}{\left(1+\text{0}\text{.03}\right)}^{\text{3}}}\right)}{{\left(1+\text{0}\text{.03}\right)}^{\text{3}}}\\ +\frac{\left(\frac{{\left(1+\text{0}\text{.03}\right)}^{\text{3}}-1}{\text{0}\text{.03}{\left(1+\text{0}\text{.03}\right)}^{\text{3}}}\right)}{{\left(1+\text{0}\text{.03}\right)}^{\text{7}}}\end{array}\right)\\ =-\text{600,000}\left(\begin{array}{l}1+\left(\frac{1.0609-1}{\text{0}\text{.03}\left(1.0609\right)}\right)+\frac{\left(\frac{1.092727-1}{\text{0}\text{.03}\left(1.092727\right)}\right)}{1.092727}\\ +\frac{\left(\frac{1.092727-1}{\text{0}\text{.03}\left(1.092727\right)}\right)}{1.229873}\end{array}\right)\\ =-\text{600,000}\left(1+\left(\frac{0.0609}{0.031827}\right)+\frac{\left(\frac{0.092727}{\text{0}\text{.03}2782}\right)}{1.092727}+\frac{\left(\frac{0.092727}{\text{0}\text{.03}2782}\right)}{1.229873}\right)\\ =-\text{600,000}\left(1+\left(1.91347\right)+\frac{\left(2.828595\right)}{1.092727}+\frac{\left(2.828595\right)}{1.229873}\right)\\ =-\text{600,000}\left(1+\left(1.91347\right)+2.588565+2.299991\right)\\ =-\text{600,000}\left(7.802026\right)\\ =-4,681,215.6\end{array}$

The present worth of plan B is -$4,681,215.6.

Plan C’s cash flow is $1,500,000 at the beginning of the year and pays $500,000 at the end of the second year. Thus, the time period (n1) is four, time period 2 (n2) is six, and time period 3 (n3) is 10. Since the interest is compounded semiannually, the semiannual interest (i1) is 3%$\left(\frac{6}{2}\right)$. The present worth of plan C (PWC) can be calculated as follows:

$\begin{array}{c}\text{PWC}=-\text{C}-\frac{\text{C1}}{{\left(1+\text{i1}\right)}^{\text{n1}}}-\frac{\text{C}}{{\left(1+\text{i1}\right)}^{\text{n2}}}-\frac{\text{C1}}{{\left(1+\text{i1}\right)}^{\text{n3}}}\\ =-1,500,000-\frac{\text{500,000}}{{\left(1+\text{0}\text{.03}\right)}^4}-\frac{\text{1,500,000}}{{\left(1+\text{0}\text{.03}\right)}^6}-\frac{\text{500,000}}{{\left(1+\text{0}\text{.03}\right)}^{10}}\\ =-1,500,000-\frac{\text{500,000}}{1.092727}-\frac{\text{1,500,000}}{1.194052}-\frac{\text{500,000}}{1.3463916}\\ =-1,500,000-457,570.8297-1,256,226.1497-371,362.872\\ =-3,555159.85\end{array}$

The present worth of plan C is -$3,555,159.85. Since the present worth of plan C is greater than other two plans, select plan C.

(b):

To determine

Spreadsheet function for calculating the present worth.

Explanation of Solution

The interest rate is filled in B2, cash flows are filled in B5 to B10. The spreadsheet function for plan A is given below:

=NPV($B2,B6:B10)+B5

The above function gives the value of -$5,202,070.

The interest rate is filled in D2, cash flows are filled in D5 to D15. The spreadsheet function for plan A is given below:

= NPV($D2,D6:D15)+D5

The above function gives the value of -$4,681,182.

The interest rate is filled in E2, and cash flows are filled in E5 to E15. The spreadsheet function for plan A is given below:

= NPV($E2,E6:E15)+E5

The above function gives the value of -$3,572,517.

Since the present worth of plan C is greater than other two plans, select plan C.