1

I am trying to create an amortisation table where the interest rate depends on two inputs provided by the user.

X represents rows and Y represents columns. Values of X, Y and interest rates are already set in a 4 X 6 table. For example if the user inputs X=2 and Y=3, then interest rate will be determined to be 5%.

The IF function could work but it would take up a lot of time and is not efficient.

I considered using an array, and I think a Vlookup will be the most efficient. In Excel I used Vlookup along with Match and it worked but I am having difficulty translating it to VBA code.

Option Explicit

Sub Amortisation()

    Dim intRate, loanLife, initLoanAmt
    Dim yrBegBal, intComp, prinComp, yrEndBal, annualPmt
    Dim outRow, rowNum, outsheet

    outRow = 3 'output table begins from row 4

    outsheet = "loan amort"
    Worksheets(outsheet).ActivateDo

    loanLife = InputBox("Enter loan life." _
      & " Loan life must be a whole number")

    If loanLife < 0 Or (loanLife - Round(loanLife) <> 0) Then
        MsgBox ("Loan life must be a whole number.")
        End
    End If

    initLoanAmt = InputBox("Enter loan amount." _
      & " Loan amount must be a positive whole number")

    If initLoanAmt < 0 Or (initLoanAmt - Round(initLoanAmt) <> 0) Then
        MsgBox ("Loan amount must be a positive whole number.")
        End
    End If

End Sub

Rather than prompting for Interest rates as I have done with the other inputs, I want VBA to use the inputs given to choose an interest rate from the table below.

This is what my table looks like.

So if X (loan life) is 5 and Y (initloanamount) is 700, then I want VBA to use 10 as the rate.

After this I can then continue with the amortisation table using PMT function.

user3598756
  • 28,893
  • 4
  • 18
  • 28
Samia
  • 13
  • 3
  • Can you post a picture of your interest rate table? And do you need to do this with VBA? – TheEngineer Jun 24 '16 at 18:34
  • Thanks for replying. Yes I have to use VBA. I don't have a picture of the interest rate table and I made a table with a similar format but I am unable to post it here. It is a simple table with four rows and 6 columns. What I need is for VBA to be able to use the inputs given to find out the interest rate at their intersection. – Samia Jun 24 '16 at 18:51
  • More information: The table range is A2:F6. The column range is A2:F2. – Samia Jun 24 '16 at 19:06
  • Can you make the table a named range? – TheEngineer Jun 24 '16 at 19:10

1 Answers1

0

Just make your table a named range called "InterestRates". Then you can access the named range in VBA as such:

Option Explicit

Sub Amortisation()

Dim intRate As Double, loanLife As Long, initLoanAmt As Long
Dim yrBegBal, intComp, prinComp, yrEndBal, annualPmt
Dim outRow, rowNum
Dim outsheet As Worksheet
Dim rng As Range

outRow = 3 'output table begins from row 4

Set outsheet = Worksheets("loan amort")
outsheet.ActivateDo

loanLife = InputBox("Enter loan life." _
  & " Loan life must be a whole number")

If loanLife < 0 Or (loanLife - Round(loanLife) <> 0) Then
    MsgBox ("Loan life must be a whole number.")
End If

initLoanAmt = InputBox("Enter loan amount." _
  & " Loan amount must be a positive whole number")

If initLoanAmt < 0 Or (initLoanAmt - Round(initLoanAmt) _
  <> 0) Then
    MsgBox ("Loan amount must be a positive whole number.")
End If

Set rng = outsheet.Range("InterestRates")

loanLife = Evaluate("MATCH(" & loanLife & ",INDEX(InterestRates,,1),0)")
initLoanAmt = Evaluate("MATCH(" & initLoanAmt & ",INDEX(InterestRates,1,),0)")

intRate = rng.Cells(loanLife, initLoanAmt).Value

End Sub

This should work for you assuming you have named your entire table (including the headers and row names) "InterestRates". This also assumes that your InterestRates range is on your "loan amort" sheet. The desired interest rate will be assigned to the variable intRate and you can add code at the end to use it however you like.

By the way, I declared some of your variables with the variable types. You should consider adding the variable types to the rest. I also removed the two End lines as they were not necessary.

TheEngineer
  • 1,205
  • 1
  • 11
  • 19
  • Thank you. My problem now is telling VBA to access the range. Maybe I can use a match function for that? This is how my code is to work. The user is prompted for a value of X, and is then prompted for a value of Y. From these two inputs, VBA is told to define interest rate as the number at intersection of X and Y gotten from the table in the worksheet. I could use If; IF x=2 and Y=3, then intRate=5..but that would take up time. On excel I use Vlookup(lookup value, table array, Match(column value, column array,0), FALSE). – Samia Jun 25 '16 at 01:18
  • Just make the coordinates within the table variables and pull the values from the user inputs. I've updated my answer to reflect this. – TheEngineer Jun 25 '16 at 01:39
  • If this answer satisfies your requirements, please mark it as the correct answer. – TheEngineer Jun 25 '16 at 01:41
  • I am still having trouble with this. I think it is because I have not specified to VBA where the rows and columns are. I tried using the Find and Intersect functions but I don't fully understand how to. I tried specifying the Row and Column ranges in addition to the overall range, and then asking VBA to find the location of X and Y within these ranges before using the intersect function to find the value in the cell at the intersection of X and Y i.e InterestRate=intersect(lookup range, row, column). – Samia Jun 25 '16 at 15:14
  • How about you update your question with the code you have and I'll take a look at it. – TheEngineer Jun 25 '16 at 15:50
  • I've updated my answer and modified your code so it should work. You will need to make your table a named range first though. – TheEngineer Jun 27 '16 at 03:37
  • Brilliant! Thank you so much for taking the time :) – Samia Jun 27 '16 at 13:31
  • Glad I could help! – TheEngineer Jun 27 '16 at 14:39