Create Function with Existing Math Formula

[bkanne]

Can anyone please help me create a VBA function based on a math formula?

The idea is to create a function that uses linear interpolation to calculate an implied rate.

The equation is as follows:

Rn = R1 + [(R2 - R1)/(T2 - T1)] * (Tn - T1)

The variables are as follows:

Rn = Implied Rate (Between Two Positions)
Tn = Time Being Measured
R1 = Lower Bound Rate
R2 = Upper Bound Rate
T1 = Lower Bound Time
T2 = Upper Bound Time

We will be solving for Rn. It would also be interesting to see this formula re-arranged to be solving for Tn instead of Rn, but I'm more focused on the former.

I think this will be pretty easy for you guys, but it will definitely be useful and greatly appreciated by me!

Thank you so much,
Ben

 

[NARAYANK991]

Hi ,

In case you want the code to do some error checking , please specify :

1. Should there be any constraint checking within the code ?

2. Should the code have default values for optional parameters , or will the function always be called with all the parameters ?

Narayan

 

[bkanne]

Constraint checking: R1 can't be greater than R2, T1 can't be greater than T2, Tn has to (or at least should be) between the T1 and T2, not sure it would make sense otherwise.

The function will always be called with all the parameters always - you need all the components to get to the end result. That should make it simpler I assume? Would be interesting to see it allow for solving for any of one of missing the variables that is not inputted...not sure if that is feasible though.

Here's what I've got so far:

Function Interpolate(Tn As Long, R1 As Long, R2 As Long, T1 As Long, T2 As Long)     

ImpliedRate = R1 + ((R2 - R1) / (T2 - T1)) * (Tn - T1) 

End Function

Hope that helps, but I'm sure you can do better...don't think that code allows for any constraints + I don't know how to set up the variable parameters (like selecting a particular cell in a sheet to designate R1, etc).

 

[NARAYANK991]

Hi ,

Can you post an example of how you would call this function from elsewhere ?

If it is from within another macro , what you have posted would work , except that you need to replace the usage of ImpliedRate by the name of the function itself Interpolate.

Thus your code could be :

Function Interpolate(Tn As Long, R1 As Long, R2 As Long, T1 As Long, T2 As Long)   

Interpolate = R1 + ((R2 - R1) / (T2 - T1)) * (Tn - T1)

End Function

If it is from a worksheet cell , then all typing would have to be of the Range type , rather than the Long type ; thus , your function would be written as :

Function Interpolate(Tn As Range, R1 As Range, R2 As Range, T1 As Range, T2 As Range)     

Interpolate = R1.Value + ((R2.Value - R1.Value) / (T2.Value - T1.Value)) * (Tn.Value - T1.Value)

End Function

In this case , it would be better if you use names other than R1 , R2 , T1 and T2 , since these may be confused with worksheet cell references.

The way the function would be used in this case would be to have it in a worksheet cell , in a formula :

=Interpolate(J17 , K32 , K33 , J12 , J13)

where I have assigned arbitrary worksheet cell references to the function's parameters.

Narayan

 

[bkanne]

Thank you so much, this is so helpful.

I've changed the names in the range, as shown below:

Function Interpolate(TimeN As Range, Rate1 As Range, Rate2 As Range, Time1 As Range, Time2 As Range)

Interpolate = Rate1.Value + ((Rate2.Value - Rate1.Value) / (Time2.Value - Time1.Value)) * (TimeN.Value - Time1.Value)

End Function

However, when I try to use the function, I'm not sure what order to put the inputs (it doesn't provide any guidance/suggestions as I populate the formula).

Is there any way to adjust this, similar to how native functions work in Excel? E.g., when you type "=PV(" into the formula bar, it returns "PV(Rate, NPer, PMT, [FV],[Type])"

Would like the same thing for this function, will make it much more usable. I've attached a sample spreadsheet that I think will be helpful.

Thank you again!

 

[p45cal]

However, when I try to use the function, I'm not sure what order to put the inputs (it doesn't provide any guidance/suggestions as I populate the formula).

Is there any way to adjust this, similar to how native functions work in Excel? E.g., when you type "=PV(" into the formula bar, it returns "PV(Rate, NPer, PMT, [FV],[Type])"

Would like the same thing for this function, will make it much more usable. I've attached a sample spreadsheet that I think will be helpful.

Thank you again!

When you get to this stage of entering the formula:

if you click the fx symbol (arrowed), you should get something like this:

Is that enough? I'm afraid it's all you'll get with simple home-grown udfs.

 

[bkanne]

Thanks!

Interestingly, I found that if you use "ctrl shift a" after entering

"=INTERPOLATE(" in the formula bar, it will return:

"=INTERPOLATE(TimeN,Rate1,Rate2,Time1,Time2)"

Shortcut works for other functions as well. I'm a big "+" user for entering formulas (very partial to the numpad), and unfortunately this doesn't seem to work if you begin your formula with a "+" symbol.

Thanks again for all the help everyone!