Question

Show all work

1. A line of credit loan of $170,000 is to be used to invest in a project. It is expected that the project will provide payments of $20,000 at the end of each year for 11 years, with the first payment at two years from now. When there is a balance owing in the account, interest is charged at ip = 4.5% per year, and when there is a surplus in the account, interest is credited at is = 3% per year. Construct an EXCEL spreadsheet showing the account balance at the end of each year until the conclusion of the Project. Hence (a) Find the Discounted Payback Period of this project. [1 mark] (b) Find the account balance at the end of 11 years, after receipt of the 10th $20,000. [1 mark]

Answer 1

First payment of $20,000 is 2 years from now, so it will be made at end of 1st year.

Book13 Q Search Sheet View 2+ Share Data = = . 3 Review der 3 3 Auto-sum General D, TX, AS 71 Home Insert Draw Page Layout Formulas X Calibri (Body) - 12 -AA = BIU A20 x ✓ fx A B C D Ep Wrap Text Merge & Centre Fill Paste .00 Conditional Format Formatting as Table Cell Styles Insert Delete Format Clear Sort & Filter Find & Select E F G H I J K L M N O Line of Credit loan $ 1,70,000 Balance interest Rates 4.50% >=0 3% Year Beginning Balance Payment* Interest** Ending Balance PVF@4.5% PV of Interest PV of Payback | 0 $ (1,70,000) $ - $ - $ (1,70,000) 1.0000 $ (1,70,000) $ 20,000 $ (7,650) $ (1,57,650) 0.9569 $ 7,321 $ 19,139 | (1,57,650) $ 20,000 $ (7,094) $ (1,44,744) 0.91571 $ 6,496 $ 18,315 | $ (1,44,744) $ 20,000 $ (6,513) $ (1,31,258) 0.8763 $ 5,708 $ 17,526 (1,31,258) $ 20,000 $ (5,907) $ (1,17,164) 0.8386 $ 4,953 $ 16,771 (1,17,164) $ 20,000 $ (5,272) $ (1,02,437) 0.8025 $ 4,231 $ 16,049 6 $ (1,02,437) $ 20,000 $ (4,610) $ (87,046) 0.7679 $ 3,540 $ 15,358 7 $ (87,046) $ 20,000 $ (3,917) $ (70,963) 0.7348 $ 2,878 $ 14,697 8 $ (70,963) $ 20,000 $ (3,193) $ (54,157) 0.7032 $ 2,246 $ 14,064 9 $ (54,157) $ 20,000 $ (2,437) $ (36,594) 0.6729 $ 1,640 $ 13,458 10 $ (36,594) $ 20,000 $ (1,647) $ (18,241) 0.6439 $ 1,060 $ 12,879 11 $ (18,241) $ 20,000 $ (821) $ 939 0.6162 $ 506 $ 12,324 Total PV $ 2,10,578 $ 1,70,578 Discounted Payback Period 1.234496363 =119/319 Sheet1 Ready @ -- + 135%

*all the payments are done at the end of the period.

** interest calculated on opening balance

PVF= (1 + id)n

Discounted Payback Period = PresentValueof Payments Initial Loan Amount + PV of Interest

Ending Balance = Beginning Balance - (Payment + Interest)

	Line of Credit loan	$ 1,70,000	Balance	Interest Rates
			<0	4.50%
			>=0	3%
Year	Beginning Balance	Payment*	Interest**	Ending Balance	PVF@4.5%	PV of Interest	PV of Payback
0	$           (1,70,000)	$            -	$          -	$      (1,70,000)	1.0000	$               -	$                -
1	$           (1,70,000)	$    20,000	$   (7,650)	$      (1,57,650)	0.9569	$         7,321	$        19,139
2	$           (1,57,650)	$    20,000	$   (7,094)	$      (1,44,744)	0.9157	$         6,496	$        18,315
3	$           (1,44,744)	$    20,000	$   (6,513)	$      (1,31,258)	0.8763	$         5,708	$        17,526
4	$           (1,31,258)	$    20,000	$   (5,907)	$      (1,17,164)	0.8386	$         4,953	$        16,771
5	$           (1,17,164)	$    20,000	$   (5,272)	$      (1,02,437)	0.8025	$         4,231	$        16,049
6	$           (1,02,437)	$    20,000	$   (4,610)	$         (87,046)	0.7679	$         3,540	$        15,358
7	$              (87,046)	$    20,000	$   (3,917)	$         (70,963)	0.7348	$         2,878	$        14,697
8	$              (70,963)	$    20,000	$   (3,193)	$         (54,157)	0.7032	$         2,246	$        14,064
9	$              (54,157)	$    20,000	$   (2,437)	$         (36,594)	0.6729	$         1,640	$        13,458
10	$              (36,594)	$    20,000	$   (1,647)	$         (18,241)	0.6439	$         1,060	$        12,879
11	$              (18,241)	$    20,000	$      (821)	$               939	0.6162	$            506	$        12,324
					Total PV	$    2,10,578	$     1,70,578
				Discounted Payback Period	1.234496363	=I19/J19

Discounted Payback Period = 1.2344years

dollars

Accountbalanceatendofi1th year = 939