Question

[Refer to the first venture (Intertactical-See reference below). Use Excel built-in functions, include in the Excel file.] (a) Use PV function to calculate the present worth (i.e., at time zero) of each year’s CF. Find the total present worth. (b) Use NPV function to calculate the present worth (i.e., at time zero) of the venture. (c) Use FV function to calculate the future worth (i.e., at year 5) of each year’s CF. Find the total future worth. (d) Use PMT function to calculate the value of equivalent uniform cash flows over the given study period of 5 years. The discount rate is 8% Reference: You have been tasked with fielding an interactive video communications systems. Your job is to provide the U.S. Army with the least expensive system (for the next 5 years) Intertactical: An interactive communications system designed to rely on current satellite systems. The Army must spend $10,590,843.42 now. (t = 0) and $1.7 million this year. (t= 1), increasing that investment by 13% in subsequent years for 4 additional years. (t = 2 through 5).

Answer 1

The cash flow of the subsequent year 2 to year 5 is given below (increasing 13% from 2nd year onwards):

year	Cash Outflow
0	(10,590,843.42)
1	(1,700,000.00)
2	1,921,000.00
3	(2,170,730.00)
4	2,452,924.90
5	(2,771,805.14)

Now below is the calculation for the present value:

year	Date	Cash Outflow	Present Value discounted @8% (i.e. Value/1.08^year)
0	1/1/2019	(10,590,843.42)	(10,590,843.42)
1	1/1/2020	(1,700,000.00)	(1,574,074.07)
2	12/31/2021	1,921,000.00	1,646,947.87
3	12/30/2024	(2,170,730.00)	(1,723,195.46)
4	12/29/2028	2,452,924.90	1,802,973.03
5	12/28/2033	(2,771,805.14)	(1,886,444.00)
		Total Present Value	(12,324,636.05)

d) To calculate the PMT amount:

H J K L (12,324,636.05) (12,324,636.05) PV PV 8% 8% rate rate nper nper =PMT(12,13,1,0,0) $3,086,784.65 PMT PMT PMT(rate, nper, pv, [fv], [type])

So, equivalent equal payment for 5 year is $3086784.65

c)

The future value of this cash flow at the end of 5 year=$18108933.79. Calculation given below:

H J K L (12,324,636.05) (12,324,636.05) PV PV 8% 8% rate rate 5 nper nper = FV(12,13,0,1,0) $18,108,933.79 FV FV FV(rate, nper, pmt, [pv], [type])

b)

NPV value would be 12324636.05. Below is the calculation (Assuming t=0 as 1/1/2019 and 365 days=1 year)

A B C Cash Outflow (10,590,843.42) (1,700,000.00) Date Prese year 1/1/2019 1/1/2020 1 2 12/31/2020 3 12/31/2021 4 12/31/2022 512/31/2023 1,921,000.00 (2,170,730.00) 2,452,924.90 (2,771,805.14) |=XNPV(8 % , C2: C7 ,B2:B7) NPV XNPV(rate, values, dates)

A B year Cash Outflow Date 1/1/2019 (10,590,843.42) 10 1/1/2020 (1,700,000.00) 1 2 12/31/2020 1,921,000.00 3 12/31/2021 (2,170,730.00) 4 12/31/2022 2,452,924.90 5 12/31/2023 (2,771,805.14) ($12,324,636.05). NPV

a)

PV calculation is below:

P R 3086784.650 PMT 3086784.650 PMT nper rate PV 5 5 nper 8% 8% rate |=PV(P3,P2,P1) ($12,324,636.05) PV PV(rate, nper, pmt, [fv], [type]l)