Writing a function in VBA that calculates future loan value

[kelseyrose]

Hey guys,

I am completely lost with how I should start creating this function in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help;">VBA</acronym>. Any help would be greatly needed to formulate this function to calculate the future loan value. I am studying for an exam and it is an optional practice exercise out of the book for the class I am taking, but I can not find the answer anywhere. Any insight will help. Thanks!!

A brokerage charges the following rates on margin loans. Write a function that will calculate the future loan value. Note that for a single loan, multiple interest rates are used on various parts of the balance. Terms are years and rates are annual effective annual rates. Interest is compounded monthly.

Hints: You cannot compound interest for multiple periods in a single calculation, as it would over-charge the borrower. One way to do this would be load upper dollar limits and rate differentials in an array (2D) or arrays (2 * 1D).
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Debit Balance[/TD]
[TD]Margin Rate[/TD]
[/TR]
[TR]
[TD]$0-$24,999.99[/TD]
[TD]Base Rate + 2%[/TD]
[/TR]
[TR]
[TD]$25,000-$49,999.99[/TD]
[TD]Base Rate + 1.5%[/TD]
[/TR]
[TR]
[TD]$50,000-$99,999.99[/TD]
[TD]Base Rate + 0.5%[/TD]
[/TR]
[TR]
[TD]$100,000-$249,999.99[/TD]
[TD]Base Rate + 0.375%[/TD]
[/TR]
[TR]
[TD]$250,000-$999,999.99[/TD]
[TD]Base Rate + 0.25%[/TD]
[/TR]
[TR]
[TD]$1,000,000-$2,499,999.99[/TD]
[TD]Base Rate -0.25%[/TD]
[/TR]
[TR]
[TD]$2,500,000+[/TD]
[TD]Base Rate - 0.50%[/TD]
[/TR]
</tbody>[/TABLE]





[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Loan[/TD]
[TD]Base Rate[/TD]
[TD]Term[/TD]
[TD]Loan FV[/TD]
[/TR]
[TR]
[TD]$66,000[/TD]
[TD]6.00%[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$255,123[/TD]
[TD]5.50%[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$1,234,567[/TD]
[TD]4.75%[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]$987,654[/TD]
[TD="bgcolor: #FAFAFA"]3.33%[/TD]
[TD="bgcolor: #FAFAFA"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

Duplicate: https://www.mrexcel.com/forum/excel...ture-loan-value-given-rates-margin-loans.html