The VBA Rate Function
Description
The VBA Rate function calculates the interest rate per period for a loan or investment.
The syntax of the function is:
RATE( Nper, Pmt,
Pv, [Fv],
[Due], [Guess] )
Where the function arguments are:
| Nper | - | The number of periods over which the loan or investment is to be paid. |
| Pmt | - | The (fixed) payment amount per period. |
| Pv | - | The present value of the loan / investment. |
| [Fv] | - |
An optional argument that specifies the future value of the loan / investment, at the end of Nper payments. If omitted, [Fv] uses the default value 0. |
| [Due] | - |
An optional argument that defines whether the payment is due at the start or the end of the period. The [Due] argument can have the value 0 or 1, meaning:
0 - the payment is due at the end of the period; If the [Due] argument is omitted, it uses the default value 0 (denoting payments due at the end of the period). |
| [Guess] | - |
An initial estimate at what the rate will be. If this argument is omitted, it will take on the default value of 10% (=0.1). (Note this is only a value for the function to start off working with - the Rate function then uses an iterative procedure to converge to the correct rate). |
Cash Flow Sign Convention:
Note that, in line with the general cash flow sign convention, cash outflows are represented by negative numbers and cash inflows are represented by positive numbers.This is seen in the examples below.
VBA Rate Function Examples
Example 1 - Calculate the Interest Rate Relating to a Loan
In the following VBA code, the VBA Rate function is used to calculate the interest rate required, with fixed payments of $1,000 per month, to pay off in full, a loan of $50,000 over a period of 5 years. The payments are due at the end of each month.
|
' Calculate the interest rate required to pay off a loan of $50,000 over
' 5 years, with payments of $1,000, due at the end of each month.
' Use the Rate function to calculate the monthly interest rate.
' Convert the calculated monthly interest rate into a yearly rate.Dim mthlyRate As Double mthlyRate = Rate( 60, -1000, 50000 ) ' mthlyRate is calculated to be 0.00618341316126619. Dim yrlyRate As Double yrlyRate = 12 * mthlyRate ' yrlyRate is calculated to be 0.0742009579351942. |
The above VBA code calculates the monthly interest rate on the loan to be 0.0062 (0.62%) and the yearly interest rate to be 0.0742 (7.42%).
Note that:
- As the payments are monthly, the total number of periods is 60 (5 years = 60 months).
- As the figures supplied to the function relate to monthly payments, the value returned from the Rate function is a monthly interest rate. This therefore needs to be multiplied by 12 to calculate the annual interest rate.
- As the payments are outgoing, the [Pmt] argument is a negative value.
- The function has omitted the [Fv] argument and so this is set to the default value 0.
- The function has omitted the [Due] argument and so this is set to the default value 0 (denoting that payments are due at the end of each period).
Example 2 - Calculate the Interest Rate Relating to an Investment
In the following VBA code, the VBA Rate function is used to calculate the interest rate required, to save $20,000, over 2 years, with a starting value of zero, and monthly savings of $800. The payments are to be due at the start of each month.
|
' Calculate the interest rate required to save $20,000 over 2 years,
' with payments of $800, due at the start of each month.
' Use the Rate function to calculate the monthly interest rate.
' Convert the calculated monthly interest rate into a yearly rate.Dim mthlyRate As Double mthlyRate = Rate( 24, -800, 0, 20000, 1 ) ' mthlyRate is calculated to be 0.00325084350169683. Dim yrlyRate As Double yrlyRate = 12 * mthlyRate ' yrlyRate is calculated to be 0.039010122020362. |
The above VBA code calculates the monthly interest rate on the investment to be 0.00325 (0.325%) and the yearly interest rate to be 0.0390 (3.9%).
Note that:
- As the payments are monthly, the total number of periods is 24 (2 years = 24 months).
- As the figures supplied to the function relate to monthly payments, the value returned from the Rate function is a monthly interest rate. This therefore needs to be multiplied by 12 to calculate the annual interest rate.
- As the payments are outgoing, the [Pmt] argument is a negative value.
VBA Rate Function Error
If the VBA Rate function fails to converge to a solution, you will get the error:
Run-time error '5': Invalid procedure call or argument
