FINA4121 HW3-1

xlsx

School

University of Minnesota-Twin Cities *

*We aren’t endorsed by this school

Course

4121

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

17

Uploaded by ColonelTitanium12456

Report
We need to determine how much interest rate risk we will have when we give out these Determine the cashflows of all loans, and total durations. Your discount rate will be in Fu You will want to do this in a clever way, instead of individually calculating them. A sheet (LoanCashflows) has been created for you. Feel free to change the format to sui Remember, use as many sheets and as much space as you need! If you are unable to do this, use the "Generic Book" sheet and determine the duration an + difficult Determine the IRR of loans Once you have determined your book's risk, let's immunize it! Issue enough debt to bring your book's duration to +/- 0.5 Issue enough debt to have +/- $5,000,000 + difficult Issue at least 2 pieces of debt + difficult Determine the IRR of debt + difficult Can you do it efficiently (lowest IRR of debt)
e loans. undingEstimates, not the rate on the loan! it your needs. nd IRR there.
FIX5 PV FIX10 PV 1 y1 $5,578,815.41 $5,275,475.57 $1,563,855.30 $1,478,822.98 2 y2 $5,578,815.41 $9,971,600.30 $1,563,855.30 $2,795,242.16 3 y3 $5,578,815.41 $14,124,073.70 $1,563,855.30 $3,959,264.82 4 y4 $5,578,815.41 $17,776,183.87 $1,563,855.30 $4,983,025.50 5 y5 $5,578,815.41 $20,972,366.13 $1,563,855.30 $5,878,980.32 6 y6 $27,894,077.05 $68,119,699.56 $1,563,855.30 $6,648,535.77 7 y7 $1,563,855.30 $7,304,456.05 8 y8 $1,563,855.30 $7,855,383.95 9 y9 $1,563,855.30 $8,316,658.68 10 y10 $1,563,855.30 $8,707,810.18 11 y11 $15,638,553.05 $57,928,180.41 12 y12 13 y13 14 y14 15 y15 16 y16 17 y17 18 y18 19 y19 20 y20 Tenor Yield Mod Duration 1 0.0575 0.9430 2 0.0578 1.8365 3 0.0582 2.6799 4 0.0585 3.4778 5 0.0587 4.2247 6 0.0591 4.9287 7 0.0595 5.5897 8 0.0599 6.2123 9 0.0602 6.7932 10 0.0603 7.3471 11 0.0608 7.8521 12 0.0613 8.3244 13 0.0618 8.7578 14 0.0623 9.1615 15 0.0628 9.5344 16 0.0639 9.8674 17 0.0648 10.1653 18 0.0651 10.4376
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
19 0.0656 10.6830 20 0.0654 10.9402
CFs FIX15 PV ADJ0510 PV ($79,500,000) $2,751,878.26 $2,602,248.94 $2,562,632.50 $2,423,293.14 $12,457,181.47 $2,751,878.26 $4,918,719.85 $2,562,632.50 $4,580,461.10 $12,457,181.47 $2,751,878.26 $6,967,022.29 $2,562,632.50 $6,487,902.47 $12,457,181.47 $2,751,878.26 $8,768,509.14 $2,562,632.50 $8,165,501.66 $12,457,181.47 $2,751,878.26 $10,345,099.12 $13,320,763.97 $50,076,569.75 $23,215,312.94 $2,751,878.26 $11,699,267.18 $23,571,293.96 $71,733,728.12 $4,315,733.56 $2,751,878.26 $12,853,474.19 $4,315,733.56 $2,751,878.26 $13,822,928.67 $4,315,733.56 $2,751,878.26 $14,634,622.61 $4,315,733.56 $2,751,878.26 $15,322,922.43 $4,315,733.56 $2,751,878.26 $15,814,420.44 $2,751,878.26 $2,751,878.26 $16,171,581.45 $2,751,878.26 $2,751,878.26 $16,406,547.36 $2,751,878.26 $2,751,878.26 $16,530,908.12 $2,751,878.26 $2,751,878.26 $16,555,690.17 $2,751,878.26 $41,278,173.89 $183,413,961.97 $0.00 $0.00 $0.00 $0.00 $0.00 IRR: Dollar Duration: Mod Duration:
PV - $ 11,779,840.635 $ 22,266,023.415 $ 31,538,263.277 $ 39,693,220.165 $ 87,273,015.311 $ 18,347,802.954 $ 20,157,930.239 $ 21,678,312.624 $ 22,951,281.287 $ 24,030,732.614 $ 15,814,420.438 $ 16,171,581.449 $ 16,406,547.356 $ 16,530,908.124 $ 16,555,690.167 $ - $ - $ - $ - $ - $ 381,195,570.06 6.301% $ 358,601,670.59 4.511
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Tenor Yield Mod DurationFunding D$ Weight 1 5.75% 0.94 $ 1,150,000.00 $ 1,084,499.22 1.4465% 2 5.78% 1.84 $ 16,000,000.00 $ 29,383,642.28 20.126% 3 5.82% 2.68 $ 20,000,000.00 $ 53,597,081.71 25.157% 4 5.85% 3.48 $ - 5 5.87% 4.22 $ - 6 5.91% 4.93 $ - 7 5.95% 5.59 $ - 8 5.99% 6.21 $ 30,000,000.00 $ 186,367,567.01 37.736% 9 6.02% 6.79 $ - 10 6.03% 7.35 $ 12,000,000.00 $ 88,164,725.18 15.094% 11 6.08% 7.85 $ - 12 6.13% 8.32 $ - 13 6.18% 8.76 $ - 14 6.23% 9.16 $ - 15 6.28% 9.53 $ - 16 6.39% 9.87 $ - 17 6.48% 10.17 $ - 18 6.51% 10.44 $ - 19 6.56% 10.68 $ - 20 6.54% 10.94 $ - $ (79,500,000.00) Totals: $ 79,150,000.00 $ 358,597,515.40 100% Loans: $ 79,500,000.00 $ 358,601,670.59 Tenor Yield Mod DurationFunding D$ Weight 1 5.75% 0.94 $ 2,000,000.00 $ 1,886,085.60 2.5157% 2 5.78% 1.84 $ 1,000,000.00 $ 1,836,477.64 1.258% 3 5.82% 2.68 $ 20,000,000.00 $ 53,597,081.71 25.157% 4 5.85% 3.48 $ - 5 5.87% 4.22 $ - 6 5.91% 4.93 $ - 7 5.95% 5.59 $ - 8 5.99% 6.21 $ 33,300,000.00 $ 206,867,999.38 41.887% 9 6.02% 6.79 $ - 10 6.03% 7.35 $ 23,000,000.00 $ 168,982,389.93 28.931% 11 6.08% 7.85 $ - 12 6.13% 8.32 $ - 13 6.18% 8.76 $ - 14 6.23% 9.16 $ -
15 6.28% 9.53 $ - 16 6.39% 9.87 $ - 17 6.48% 10.17 $ - 18 6.51% 10.44 $ - 19 6.56% 10.68 $ - 20 6.54% 10.94 $ - $ (79,500,000.00) Totals: $ 79,300,000.00 $ 433,170,034.26 100% Loans: $ 79,500,000.00 $ 358,601,670.59
Duration PV Book’s duration between -0.5 to 0.5, and yo 0.0136 $ 1,087,470.45 0.3696 $ 28,598,473.54 Loan: IRR: 6.301% 0.6742 $ 50,634,669.40 Dollar Duration: $ 358,601,670.59 Mod Duration: 4.511 4.511 =W1D1+W2D2+W3D3 2.3442 $ 150,692,661.89 Debt: IRR: 0.173% Dollar Duration: $ 358,597,515.40 1.1090 $ 66,818,024.59 Mod Duration: 4.511 4.51 $ 297,831,299.86 Duration PV Loan: IRR: 6.301% 0.0237 $ 1,891,252.96 Dollar Duration: $ 358,601,670.59 0.0231 $ 1,787,404.60 Mod Duration: 4.511 0.6742 $ 50,634,669.40 Debt: IRR: 0.122% Dollar Duration: $ 349,223,518.57 Mod Duration: 4.404 2.6021 $ 167,268,854.70 2.1256 $ 128,067,880.46
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5.45 $ 349,650,062.11
ou should issue enough debt to be within $5 million of the total loan book
Loan Duration 4.51 Loan Dollar Duration $ 358,601,670.59 Debt Duration 4.51 Debt Dollar Duration $ 358,597,515.40 Portfolio After Duration $ 79,150,000.00 Tenor Issue Amount 1 $ 1,150,000.00 2 $ 16,000,000.00 3 $ 20,000,000.00 4 - 5 - 6 - 7 - 8 $ 30,000,000.00 9 - 10 $ 12,000,000.00 11 - 12 - 13 - 14 - 15 - 16 - 17 - 18 - 19 - 20 -
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
ProductID Rate FIX5 6.000% Fixed for whole 5 years. FIX10 6.250% Fixed for whole 10 years. FIX15 6.500% Fixed for whole 15 years. ADJ0510 6.125% Fixed for 5 years, but amortization schedule is as if it's being paid over 10. FIX5 FIX10 FIX15 ADJ0510 18750000 500000 250000 1500000 5000000 $17,335,805.00 1500000 125000 750000 250000 $15,834,990.56 125000 500000 5000000 1000000 $14,242,251.23 1000000 1000000 500000 125000 $12,551,956.62 750000 1500000 500000 750000 $13,320,763.97 250000 500000 125000 500000 125000 1000000 1500000 5000000 5000000 5000000 1000000 5000000 1000000 1500000 1000000 125000 250000 11,375,000 750000 750000 1500000 125000 125000 5000000 ($1,563,855.30) 500000 125000 1500000 5000000 18,750,000.00 5000000 1000000 23,500,000 125000 ($2,562,632.50) 5000000 ($5,578,815.41) 1500000 25,875,000 ($2,751,878.26)
. In year 5, customer has balloon payment of rest of the balance.
Borrow amount APR ProductID $250,000 6.250% FIX10 $1,500,000 6.500% FIX15 $5,000,000 6.125% ADJ0510 $750,000 6.500% FIX15 $125,000 6.250% FIX10 $500,000 6.000% FIX5 $500,000 6.250% FIX10 $1,500,000 6.000% FIX5 $5,000,000 6.500% FIX15 $500,000 6.500% FIX15 $1,000,000 6.250% FIX10 $500,000 6.500% FIX15 $125,000 6.500% FIX15 $1,500,000 6.500% FIX15 $1,500,000 6.250% FIX10 $125,000 6.000% FIX5 $1,000,000 6.500% FIX15 $250,000 6.125% ADJ0510 $1,000,000 6.125% ADJ0510 $500,000 6.250% FIX10 $1,000,000 6.000% FIX5 $1,000,000 6.500% FIX15 $750,000 6.000% FIX5 $750,000 6.500% FIX15 $250,000 6.000% FIX5 $125,000 6.000% FIX5 $5,000,000 6.000% FIX5 $125,000 6.500% FIX15 $1,000,000 6.000% FIX5 $500,000 6.500% FIX15 $250,000 6.000% FIX5 $125,000 6.125% ADJ0510 $750,000 6.125% ADJ0510 $1,000,000 6.250% FIX10 $500,000 6.125% ADJ0510 $5,000,000 6.500% FIX15 $5,000,000 6.125% ADJ0510 $5,000,000 6.125% ADJ0510 $1,000,000 6.500% FIX15 $1,500,000 6.000% FIX5 $125,000 6.125% ADJ0510 $5,000,000 6.000% FIX5 $125,000 6.500% FIX15 $750,000 6.125% ADJ0510
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
$5,000,000 6.250% FIX10 $1,500,000 6.250% FIX10 $125,000 6.125% ADJ0510 $1,500,000 6.000% FIX5 $125,000 6.125% ADJ0510 $5,000,000 6.000% FIX5 $5,000,000 6.500% FIX15 $1,500,000 6.500% FIX15 $79,500,000
Loan Cashflows $111,594,234.42 Duration of Cashflows Dollar duration y1 $14,000,000 y2 $14,000,000 y3 $14,000,000 y4 $14,000,000 y5 $20,594,234 y6 $5,000,000 y7 $5,000,000 y8 $5,000,000 y9 $5,000,000 y10 $3,000,000 y11 $3,000,000 y12 $3,000,000 y13 $3,000,000 y14 $3,000,000 y15 y16 y17 y18 y19 y20