This chapter is from the book
This chapter is from the book
This chapter is from the book
Using Financial Functions
Financial functions are used to perform many standard financial calculations such as interest rates, annuity or loan payments, and depreciation. Following are some financial functions you might find useful.
The Ddb Function
The Dbd function calculates the depreciation of an asset for a specified time using the predefined double-declining balance method. The following is the syntax for this function, where cost is an expression representing the asset's opening cost and salvage is an expression that specifies the value of the asset at the end of life, an expression representing the term of the asset's lifespan.
Ddb(cost, salvage, life, period[, factor])
The period argument represents the time span for which the depreciation is being calculated. All these arguments use Double data types. There is an optional factor argument that specifies the rate of decline of the asset. If omitted, the double-declining method is used.
The FV Function
The FV function is used to calculate the future value of an annuity. The FV function returns a double data type and uses the syntax
FV(rate, nper,pmt[,pv [, type]])
where rate is an expression resulting in a Double data type that represents the interest rate per period, nper is an expression resulting in an Integer data type that represents the number of payment periods in the annuity, and pmt is an expression resulting in a Double value that specifies the payment being made for each period. There are two optional arguments, pv and type, which are Variant data types that specify the present value of the annuity and whether payments are made at the start or end of each period.
The Pmt Function
The Pmt function is used to calculate the payment for an annuity or loan. This function uses the syntax
Pmt(rate, nper, pv[, fv[, type]])
where rate is an expression resulting in a Double data type that represents the interest rate per period, nper is an integer expression that defines the number of payments to be made, and pv identifies the present value and is also a Double data type. There are two optional arguments, fv and type, which are Variant data types that represent the future value of the payments and whether payments are made at the start or end of each period.
The Rate Function
The Rate function is used to calculate the periodic interest rate for an annuity or loan. The syntax for this function is
Rate(nper, pmt, pv[, fv[, type[, guess]]])
Where nper is an expression resulting in a Double data type that represents the number of period, pmt is an expression resulting in a Double data type that represents the payment per period, and pv is an expression resulting in a Double data type that defines the present value. There are also three optional arguments: fv, type, and guess, which identify the future value, determine whether payments are made at the start or end of each period, and allow you to give an estimate of the rate, respectively.
A Financial Functions Example
In keeping with the Inventory application theme of the sample file, this example looks at a scenario where you want to expand to cover a new product line. Because this new product line is from a new vendor, the vendor requires you to make a significant purchase the first time around. You don't have the $10,000 to make the initial purchase, so you need to figure out different loan scenarios to see whether you can afford a loan.
- Open the basUDFs module or one of your own.
- Enter the following procedure:
Public function LoanPmt(dblRate As Double, intNper As Integer, _ dblPv As Double) As Currency LoanPmt = Abs(Pmt(dblRate/12, intNper, dblPv)) End Function - In the Immediate window enter the following statement and press Enter:
? LoanPmt(.05,36,10000)
Figure 4.4 shows the code and the result. This loan would cost you $300 per month for 36 months. You can now try out different scenarios with combinations of rate and term to see what your payments might be.
)
Figure 4.4 The LoanPmt function and its results.