Question

Here are the net cash flows for a project your company is considering: Year 0 = -1000 Year 1 = 250 Year 2 = 449 Year 3 = 800 Year 4 = 500 Year 5 = 500 If the payback period with interest is 3 years, for what range of interest rates is this project worth doing (start your analysis with an interest rate of 0%)? ** Kindly just don’t directly draw the table with values in it, it’ll be helpful to include explanation of how you get to fill the table . Already I posted this question earlier and noticed that someone had just randomly written someone else’s who has solved the question with no change in word format too.

Answer 1

ANSWER:

SINCE TABLES ARE TO BE MADE FOR VARIOUS INTEREST RATES, I HAVE DONE IN EXCEL AS MANUALLY IT WILL TAKE MORE THEN 2 HOURS, STARTING WITH 0%.

WE WILL NEED TO FIND THE PRESENT WORTH AT VARIOUS RATE STARTING FROM 0% AND THEN WE WILL FIND THE BALANCE FOR EACH YEAR AS FOLLOWS.

BALANCE FOR YEAR 0 = PV AT YEAR 0

BALANCE FOR YEAR 1 = BALANCE AT YEAR 0 + PV AT YEAR 1

BALANCE FOR YEAR 2 = BALANCE AT YEAR 1 + PV AT YEAR 2

BALANCE FOR YEAR 3 = BALANCE AT YEAR 2 + PV AT YEAR 3

BALANCE FOR YEAR 4 = BALANCE AT YEAR 3 + PV AT YEAR 4

BALANCE FOR YEAR 5 = BALANCE AT YEAR 4 + PV AT YEAR 5

PAYBACK PERIOD = YEAR IN WHICH THE BALANCE WAS LAST NEGATIVE - ( BALANCE IN WHICH THE YEAR IT WAS LAST NEGATIVE / PV OF THE YEAR IN WHICH THE BALANCE WAS POSITIVE)

PV IN EXCEL IS CALCULATED AS =-PV(RATE,NPER,PMT,FV,TYPE)

WHERE RATE WILL VARY FROM 0% TILL WE FIND THE PAYBACK PERIOD TO BE MORE THEN 3 YEARS .

NPER = NO OF YEARS WHICH WILL BE (0,1,2,3,4,5) FOR EACH OF THE CASHFLOWS.

PMT = WE WILL LEAVE IT BLANK BECAUSE THERE IS NO NEED TO PUT IT AS THERE ARE CASH FLOWS

FV = CASH FLOWS OF EACH YEAR

TYPE = WE WILL LEAVE IT BLANK AS IT WILL CALCULATED IT TO BE THE END OF THE PERIOD.

year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5
cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500
pv at 0%	-1000	$250.00	$449.00	$800.00	$500.00	$500.00		pv at 3%	-1000	$242.72	$423.23	$732.11	$444.24	$431.30		pv at 6%	-1000	$235.85	$399.61	$671.70	$396.05	$373.63		pv at 9%	-1000	$229.36	$377.91	$617.75	$354.21	$324.97		pv at 12%	-1000	$223.21	$357.94	$569.42	$317.76	$283.71		pv at 15%	-1000	$217.39	$339.51	$526.01	$285.88	$248.59		pv at 18%	-1000	$211.86	$322.46	$486.90	$257.89	$218.55
balance	-1000	($750.00)	($301.00)	$499.00	$999.00	$1,499.00		balance	-1000	($757.28)	($334.06)	$398.06	$842.30	$1,273.61		balance	-1000	($764.15)	($364.54)	$307.15	$703.20	$1,076.83		balance	-1000	($770.64)	($392.73)	$225.02	$579.23	$904.20		balance	-1000	($776.79)	($418.85)	$150.58	$468.34	$752.05		balance	-1000	($782.61)	($443.10)	$82.91	$368.79	$617.38		balance	-1000	($788.14)	($465.67)	$21.23	$279.13	$497.68
payback period	2.37625							payback period	2.45629							payback period	2.54272							payback period	2.635743							payback period	2.73556							payback period	2.842375							payback period	2.95639
year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5
cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500
pv at 1%	-1000	$247.52	$440.15	$776.47	$480.49	$475.73		pv at 4%	-1000	$240.38	$415.13	$711.20	$427.40	$410.96		pv at 7%	-1000	$233.64	$392.17	$653.04	$381.45	$356.49		pv at 10%	-1000	$227.27	$371.07	$601.05	$341.51	$310.46		pv at 13%	-1000	$221.24	$351.63	$554.44	$306.66	$271.38		pv at 16%	-1000	$215.52	$333.68	$512.53	$276.15	$238.06		pv at 19%	-1000	$210.08	$317.07	$474.73	$249.33	$209.52
balance	-1000	($752.48)	($312.32)	$464.15	$944.64	$1,420.37		balance	-1000	($759.62)	($344.49)	$366.71	$794.11	$1,205.07		balance	-1000	($766.36)	($374.18)	$278.86	$660.30	$1,016.80		balance	-1000	($772.73)	($401.65)	$199.40	$540.91	$851.37		balance	-1000	($778.76)	($427.13)	$127.31	$433.97	$705.35		balance	-1000	($784.48)	($450.80)	$61.72	$337.87	$575.93		balance	-1000	($789.92)	($472.85)	$1.88	$251.22	$460.74
payback period	2.402233							payback period	2.48438							payback period	2.572985							payback period	2.66825							payback period	2.770378							payback period	2.87957							payback period	2.99603
year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5		year	0	1	2	3	4	5
cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500		cash flow	-1000	250	449	800	500	500
pv at 2%	-1000	$245.10	$431.56	$753.86	$461.92	$452.87		pv at 5%	-1000	$238.10	$407.26	$691.07	$411.35	$391.76		pv at 8%	-1000	$231.48	$384.95	$635.07	$367.51	$340.29		pv at 11%	-1000	$225.23	$364.42	$584.95	$329.37	$296.73		pv at 14%	-1000	$219.30	$345.49	$539.98	$296.04	$259.68		pv at 17%	-1000	$213.68	$328.00	$499.50	$266.83	$228.06		pv at 20%	-1000	$208.33	$311.81	$462.96	$241.13	$200.94
balance	-1000	($754.90)	($323.34)	$430.52	$892.44	$1,345.31		balance	-1000	($761.90)	($354.65)	$336.42	$747.77	$1,139.54		balance	-1000	($768.52)	($383.57)	$251.49	$619.01	$959.30		balance	-1000	($774.77)	($410.36)	$174.60	$503.96	$800.69		balance	-1000	($780.70)	($435.21)	$104.77	$400.81	$660.49		balance	-1000	($786.32)	($458.32)	$41.17	$308.00	$536.05		balance	-1000	($791.67)	($479.86)	($16.90)	$224.23	$425.17
payback period	2.42891							payback period	2.513188							payback period	2.60399							payback period	2.70152							payback period	2.80598							payback period	2.917573							payback period	3.0365

AS WE CAN SEE FROM THESE TABLES THAT RATES AT WHICH THE PROJECT IS WORTH FOR THE PAYBACK PERIOD OF 3 YEARS IS FROM 0% TO 19% BECAUSE AT 20% INTEREST RATE , THE PAYBACK PERIOD WILL BE MORE THEN 3 YEARS.

PV AT 0% IS CALCULATED AS =-PV(RATE,NPER,PMT,FV,TYPE) =-PV(0%,0,,-1000,,)

SIMILARLY FOR EVERY CASH FLOW THE RATES, NPER AND FV (CASH FLOWS) WILL CHANGE.

SO OUR RANGE IS FROM 0% TO 19%.