Writing a code for Future Loan Value with given rates on margin loans

[kelseyrose]

Hi guys,

I am completely lost with I should start this code 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 have an exam and this was a practice problem in the book and I can not seem to figure it out.

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]

 

[joeu2004]

I am completely lost with I should start this code 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>.

Does the practice question require that you use VBA? I don't see why VBA would be necessary.

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.

I don't much about margin loans. But I'm not sure the instructor (practice test writer) does either. I cannot imagine any investor who would assume the risk of a margin loan for "years". Take a look at http://www.investopedia.com/ask/answers/07/margin_interest.asp]How is margin interest calculated? (click here), for example.

Anyway, that's real life. It is best to stick with whatever the instructor wants.

After-thought.... Perhaps my confusion is due to terminology. I read "brokerage" and "margin loan" as referring to investments. Is the practice test question talking about something else?

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).

Given the conflicting interpretation of "margin loan" here, I confess that this hint confuses me. I wonder if there are some details in the practice question that you did not share.

Anyway, as I (mis?)understand the problem, I suggest that you study VLOOKUP. Perhaps that will give you some idea about how to solve this with an Excel formula instead of VBA.

But I also do not see why "rate differentials" would be necessary. They are used when the interpretation of that rates are, for example, 2% on the first $24,499.99, 1.5% on the next $25,000 (up to $49,499.99), etc.

Is that the interpretation of the "margin rate" table that the practice question describes?

If so, you can use SUMPRODUCT for that kind of calculation. Take a look at http://www.mcgimpsey.com/excel/variablerate.html (click here).

PS.... And if that is indeed the intended interpretation of the "margin rate" table, then yes, a VBA implementation might be easier to write, if not warranted. An Excel formula for that might be a convoluted array-entered formula, if that is even feasible. (I'd have to give it more thought. But it's not worth the effort, IMHO.)

 

[StephenCrump]

kelseyrose, welcome to the Forum!

My very quick take on this, and joeu2004's comments/questions ...

- For "margin loan", you could read "loan" without any loss of meaning

- Based on the wording of the question, $66,000 for two periods @ 6% base:

--> $25,000 @8%, $25,000 @7.5%, $16,000 at 6.5% --> balance $70,915 at time 1
--> $25,000 @8%, $25,000 @7.5%, $20,915 at 6.5% --> balance $76,149.48 at time 2

hence the hint about not compounding and over-charging the borrower, because over time as the balance increases, the average interest rate charged will reduce.

kelseyrose, does your exercise provide an answer?

 

[kelseyrose]

No, it does not! We are supposed to create a user-defined function in VBA to perform this. I knew how to do the calculations you just provided me with, thank you for that, but I am unsure of how to create the function. Thank you for all of your help!

 

[StephenCrump]

Given it's your practice for your exam, you might want to have a go at attempting some VBA code? We can help you with specific problems.

Myself, I'd be looking at passing the Loan Amount, Base Rate, Term and the Interest Rate table as arguments to the function. (Hint: Think of how you'd create a VLOOKUP table for the stepped interest rates).

Your VBA code will need to loop from 1 to N, where N is the loan term, calculating the loan balance at the end of each period.

 

[joeu2004]

- For "margin loan", you could read "loan" without any loss of meaning
- Based on the wording of the question, $66,000 for two periods @ 6% base:
--> $25,000 @8%, $25,000 @7.5%, $16,000 at 6.5% --> balance $70,915 at time 1
--> $25,000 @8%, $25,000 @7.5%, $20,915 at 6.5% --> balance $76,149.48 at time 2

You seem to understand the problem better than I. Sorry for the misdirections. But I never heard of a "loan" with an increasing FV, other than a negative amortization.

Can you explain the loan model that you talking about here, for my edification?

 

[StephenCrump]

You seem to understand the problem better than I. Sorry for the misdirections. But I never heard of a "loan" with an increasing FV, other than a negative amortization.

Can you explain the loan model that you talking about here, for my edification?

No problem. It's actually just the basic: Loan outstanding at time n = Amount borrowed * (1+i)^n. But with the complication here that i is tiered.

With a margin loan, there will be an agreed credit limit, and there will also be specified loan valuation ratios (LVRs), which in Australia are typically around 75% for blue chips, lower for smaller caps / potentially riskier shares.

The borrower may start by borrowing a smaller amount. There will be no particular repayment schedule, although the borrower may choose to apply dividends and distributions against the loan balance to keep it from running away.

If the loan balance does increase over time, it won't concern the loan provider provided it stays within the agreed credit limit, and doesn't exceed the LVRs.

 

[joeu2004]

With a margin loan, there will be an agreed credit limit, and there will also be specified loan valuation ratios (LVRs), which in Australia are typically around 75% for blue chips, lower for smaller caps / potentially riskier shares. [....] There will be no particular repayment schedule

Thanks. I read up on margin trading, so I understand now what you are talking about.

 

[kelseyrose]

Function fvalue(loan, base, term)






Dim ub, lb, rates
lb = Array(0, 25000, 50000, 100000, 250000, 1000000, 2500000)
ub = Array(25000, 50000, 100000, 250000, 1000000, 50000000)
rates = Array(0.02, 0.015, 0.005, 0.00375, 0.0025, -0.0025, -0.005)
bal = loan
newbal = 0
For j = 1 To term * 12
For i = 0 To 6
If bal > ub(i) Then
newbal = newbal + (ub(i) - lb(i)) * (1 + (base + rates) / 12)
ElseIf bal > lb(i) Then
newbal = newbal + (bal - lb(i)) * (1 + (base + rates) / 12)


End If
Next i
bal = newbal
newbal = 0
Next j
fvalue = bal




End Function


This is what I have so far but it is not working. I know i have to use the option explicit but am unsure. I can't figure out how to get the arrays looping and using Option Base 1 or the For loop 0 to 6.

 

[StephenCrump]

That's a great start! Just a couple of things to get it producing results ....

- Because rates is an array, you're going to need:

If bal > ub(i) Then
    newbal = newbal + (ub(i) - lb(i)) * (1 + (base + rates[COLOR=#ff0000][B](i)[/B][/COLOR]) / 12)
ElseIf bal > lb(i) Then
    newbal = newbal + (bal - lb(i)) * (1 + (base + rates[COLOR=#ff0000][B](i)[/B][/COLOR]) / 12)
Endif

- You're also going to need 7 values in ub. The last can be some arbitrarily big number.

It's good that you've recognised the potential Option Base issue. Looping through the VB arrays from 0 to 6 will work with Option Base 0, but for Option Base 1 you'll need 1 to 7. The smarter way to accommodate either (and a good way in general to loop, rather than relying on hard-coded numbers) would be:

For i = LBound(lb) To UBound(lb)

Finally, using Option Explicit is really good practice, and potentially will save you heaps of time in debugging future code. Once you get the function producing results that you can check, you might want to think about declaring all your variables, plus specifying the types of your function arguments and the function itself.