Visual Basic for Applications Reference
This example uses the MIRR function to return the modified internal rate of return for a series of cash flows contained in the array
LoanAPR represents the financing interest, and
InvAPR represents the interest rate received on reinvestment.
Dim LoanAPR, InvAPR, Fmt, RetRate, Msg Static Values(5) As Double ' Set up array. LoanAPR = .1 ' Loan rate. InvAPR = .12 ' Reinvestment rate. Fmt = "#0.00" ' Define money format. Values(0) = -70000 ' Business start-up costs. ' Positive cash flows reflecting income for four successive years. Values(1) = 22000 : Values(2) = 25000 Values(3) = 28000 : Values(4) = 31000 RetRate = MIRR(Values(), LoanAPR, InvAPR) ' Calculate internal rate. Msg = "The modified internal rate of return for these five cash flows is" Msg = Msg & Format(Abs(RetRate) * 100, Fmt) & "%." MsgBox Msg ' Display internal return ' rate.