How was the market value calculated?

1. You are asked to determine the market value(mark-tomarket) balance sheet for Angus State Bankand loan duration (amounts in $ thousands and duration in years): Book Value Market Value Assets Amount Amount Duration T-bills $ 180 $ 180 0.50 Loans" 5.000 _ Total Assets 5,180 _ Liabilities Deposits 4.184 4.184 0.50 Total Liabilities 4,184 4.184 Equity 996 _ Total Lia and NW 5.180 _ 1Since this is a simple bank. it has only one type of loan. The loan has a $5,000 book value (current outstanding principal). amortized loan with annual payments. an interest rate of 6.5 percent. and 20- years to maturity. Similar amortized loans today (market interest rate for similar loans) have an Interest rate of 7 percent which, is the market yleld. a.Using Excel. determine the market value and duration of the loan and ll in the blanks in the balance sheet above. Please include a copyI of your Excel Spreadsheet with your completed exam (you can copy and paste as a picture). The market value of loan is $4735.15. Duration of the loan is as follows: Duration = 11.50