 | |
#1
 
| |||
| |||
|
Lease Calculations -
12-02-2005
, 03:23 PM
#2
| |||
| |||
|
|
Does anyone know the formula for lease calculations, ie. automobile lease for a certain amount of months, knowing the residual, term, and rate factor? |
but check FileMaker's "Financial Calculations" section and you may find
the function is already built-in.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
#3
| |||
| |||
|
|
In article <1133558611.802313.18600 (AT) g49g2000cwa (DOT) googlegroups.com>, "JC" jc (AT) jclewis (DOT) biz> wrote: Does anyone know the formula for lease calculations, ie. automobile lease for a certain amount of months, knowing the residual, term, and rate factor? I've never done any accounting, so don't know what that calculation is, but check FileMaker's "Financial Calculations" section and you may find the function is already built-in. Helpful Harry Hopefully helping harassed humans happily handle handiwork hardships ;o) |
#4
| |||
| |||
|
|
Does anyone know the formula for lease calculations, ie. automobile lease for a certain amount of months, knowing the residual, term, and rate factor? Thanks jc They vary slightly from manufacturer or bank. Which lending institution |
fees, etc...
or just a balloon loan? Which also varies from bank to bank.
By the way, it takes a series of calculations and choices. A good lease
calculator will have to handle many different senarios, for example,
there are upfront fees associated with most leases, which can be paid up
front or capitalized, there are often ways to waive acquisition fees or
security deposits by adding to the money factor or interest rate.
Sometimes the are simply waived as part of a loyalty program provided by
the manufacturer. There are other issues regarding sales and property
taxes, which are part of the monthly payment in leases, and of course
these vary from state to state.
If you can provide some additional details, I'm sure I can help.
Michael Myett
#5
| |||
| |||
|
|
JC wrote: Does anyone know the formula for lease calculations, ie. automobile lease for a certain amount of months, knowing the residual, term, and rate factor? Thanks jc They vary slightly from manufacturer or bank. Which lending institution would you are you trying to emulate? is it a true lease with acquisition fees, etc... or just a balloon loan? Which also varies from bank to bank. By the way, it takes a series of calculations and choices. A good lease calculator will have to handle many different senarios, for example, there are upfront fees associated with most leases, which can be paid up front or capitalized, there are often ways to waive acquisition fees or security deposits by adding to the money factor or interest rate. Sometimes the are simply waived as part of a loyalty program provided by the manufacturer. There are other issues regarding sales and property taxes, which are part of the monthly payment in leases, and of course these vary from state to state. If you can provide some additional details, I'm sure I can help. Michael Myett |
arithmetic, the only 'hard' stuff is unravelling the stuff involving
compound interest, which Filemaker has built in anyway, at least
rudimentarily.
I say rudimentarily, because the functions don't support several of the
more advanced options you'd find in say Microsoft Excel or
Openoffice.org Calc. (stuff like specifying if the payment is made at
the beginning or end of the period, or computing the payment of loan
with a non-zero principle at the end -- where you'll have some 'extra'
compound interest.
(But all these options can be implemented manually using the existing
functions to do the grunt work if you have an understanding of the
underlying math.)
#6
| |||
| |||
|
|
This is an article I wrote many moons ago that might be handy to keep around... Building Your Own Annuity (Financial) Functions You may need to calculate a very specific kind of annuity (financial) function, but the appropriate calculation is not a built-in function in the program you are using. Or, the function used by the program does not include enough arguments to specify all the options you want in the calculation. (For example, all FileMaker Pro financial functions assume that payments/debits are made at the end of a period, not at the beginning.) All financial functions are based on one large, complex formula that can be re-arranged (using standard algebraic techniques) to obtain whatever value you want. Let's look at a formula, and an example of how to derive a function from it. The Master Formula: when rate *<> 0: pv * (1 + rate)^nper + pmt * (1 + rate * type) * (((1 + rate)^nper - 1) / rate) + fv = 0 when rate = 0: pv + pmt * nper + fv = 0 in this formula, pv = the present value of the investment rate = the interest rate each period nper = the number of periods pmt = the payment amount each period type = when payments are made. (0 = end of the period, 1 = beginning of the period.) fv = the future value of the investment Note: Expenses (negative cash flow) are represented by negative numbers. Income (positive cash flow) is represented by positive numbers. For example, pmt is usually a negative number. So, suppose that you wanted to find out the payment required for an investment when the payments are made at the beginning of a period (the FileMaker Pro PMT function works only with payments made at the end of a period.) Here is how you would derive the needed formula: 1. Realize that your end goal is to isolate the PMT variable on one side of the equals sign. You must be able to supply all other variables. Because the interest rate in this case is not zero, we'll use the first formula. 2. PMT is buried inside the formula in a way that does not let us simply subtract it from both sides or divide both sides by it. So we have to move step-by step to simplify the component of the equation that contains PMT. a. Subtract FV from both sides. pv * (1 + rate)^nper + pmt * (1 + rate * type) * (((1 + rate)^nper - 1) / rate) = -fv b. Subtract pv * (1 + rate)^nper from both sides. pmt * (1 + rate * type) * (((1 + rate)^nper - 1) / rate) = -fv - pv * (1 + rate)^nper c. Divide both sides by (1 + rate * type) * (((1 + rate)^nper - 1) / rate) pmt = (-fv - pv * (1 + rate)^nper) / ((1 + rate * type) * (((1 + rate)^nper - 1) / rate)) You can now fill in the values for the variables you know (fv, pv, rate, nper, and type) and FileMaker Pro will calculate the correct payment. Note: Due to the complexity of the equation, the Rate variable cannot be solved by itself with basic algebraic methods. Other programs that do solve for Rate do it through an iterative process (essentially guessing at a probable number and continuing to guess until the number fits). Bill "George" <g.sova (AT) comcast (DOT) net> wrote in message news:1133111447.388470.263180 (AT) g14g2000cwa (DOT) googlegroups.com... Dear JC: The payoff usually means the remaining balance of the original loan amount.In that case you would use the formula PV(periodic payment, interest rate per period, number of periods). For this formula to work, certain conditions are necessary: 1. each periodic payment is the same; 2. the interest rate is unchanged for the entire period of time; 3. each period is of the same length (usually a month) If all three above conditions are not met, than you have to prepare an amortization schedule for all payments, reflecting in each payment made the amount of payment, the length of the period (the number of days), and the interest charged for the period. It becomes messy, but it would be the only method to compute how much of principal was repaid for the period. George JC wrote: I have the following fields set up. Amount_Financed, Apr, Term, Payment, Payments made What would be the calculation to determine the payoff (doesn't have to be to the penny) Thanks JC |
Quote:
|
In article <kv5kf.522$MM.388 (AT) fe16 (DOT) usenetserver.com>, michael.myett (AT) adelphia (DOT) net says... JC wrote: Does anyone know the formula for lease calculations, ie. automobile lease for a certain amount of months, knowing the residual, term, and rate factor? Thanks jc They vary slightly from manufacturer or bank. Which lending institution would you are you trying to emulate? is it a true lease with acquisition fees, etc... or just a balloon loan? Which also varies from bank to bank. By the way, it takes a series of calculations and choices. A good lease calculator will have to handle many different senarios, for example, there are upfront fees associated with most leases, which can be paid up front or capitalized, there are often ways to waive acquisition fees or security deposits by adding to the money factor or interest rate. Sometimes the are simply waived as part of a loyalty program provided by the manufacturer. There are other issues regarding sales and property taxes, which are part of the monthly payment in leases, and of course these vary from state to state. If you can provide some additional details, I'm sure I can help. Michael Myett Most the 'variation' you are discussing can be handled with basic arithmetic, the only 'hard' stuff is unravelling the stuff involving compound interest, which Filemaker has built in anyway, at least rudimentarily. I say rudimentarily, because the functions don't support several of the more advanced options you'd find in say Microsoft Excel or Openoffice.org Calc. (stuff like specifying if the payment is made at the beginning or end of the period, or computing the payment of loan with a non-zero principle at the end -- where you'll have some 'extra' compound interest. (But all these options can be implemented manually using the existing functions to do the grunt work if you have an understanding of the underlying math.) |
#7
| |||
| |||
|
|
In other words, the article I posted the 27th to this poster's previous question. "Bill Marriott" <wjm (AT) wjm (DOT) org> wrote in message news  emdnTjvea9ioxfenZ2dnUVZ_tudnZ2d (AT) comcast (DOT) com...This is an article I wrote many moons ago that might be handy to keep around... Building Your Own Annuity (Financial) Functions You may need to calculate a very specific kind of annuity (financial) function, but the appropriate calculation is not a built-in function in the program you are using. Or, the function used by the program does not include enough arguments to specify all the options you want in the calculation. (For example, all FileMaker Pro financial functions assume that payments/debits are made at the end of a period, not at the beginning.) All financial functions are based on one large, complex formula that can be re-arranged (using standard algebraic techniques) to obtain whatever value you want. Let's look at a formula, and an example of how to derive a function from it. The Master Formula: when rate *<> 0: pv * (1 + rate)^nper + pmt * (1 + rate * type) * (((1 + rate)^nper - 1) / rate) + fv = 0 when rate = 0: pv + pmt * nper + fv = 0 in this formula, pv = the present value of the investment rate = the interest rate each period nper = the number of periods pmt = the payment amount each period type = when payments are made. (0 = end of the period, 1 = beginning of the period.) fv = the future value of the investment Note: Expenses (negative cash flow) are represented by negative numbers. Income (positive cash flow) is represented by positive numbers. For example, pmt is usually a negative number. So, suppose that you wanted to find out the payment required for an investment when the payments are made at the beginning of a period (the FileMaker Pro PMT function works only with payments made at the end of a period.) Here is how you would derive the needed formula: 1. Realize that your end goal is to isolate the PMT variable on one side of the equals sign. You must be able to supply all other variables. Because the interest rate in this case is not zero, we'll use the first formula. 2. PMT is buried inside the formula in a way that does not let us simply subtract it from both sides or divide both sides by it. So we have to move step-by step to simplify the component of the equation that contains PMT. a. Subtract FV from both sides. pv * (1 + rate)^nper + pmt * (1 + rate * type) * (((1 + rate)^nper - 1) / rate) = -fv b. Subtract pv * (1 + rate)^nper from both sides. pmt * (1 + rate * type) * (((1 + rate)^nper - 1) / rate) = -fv - pv * (1 + rate)^nper c. Divide both sides by (1 + rate * type) * (((1 + rate)^nper - 1) / rate) pmt = (-fv - pv * (1 + rate)^nper) / ((1 + rate * type) * (((1 + rate)^nper - 1) / rate)) You can now fill in the values for the variables you know (fv, pv, rate, nper, and type) and FileMaker Pro will calculate the correct payment. Note: Due to the complexity of the equation, the Rate variable cannot be solved by itself with basic algebraic methods. Other programs that do solve for Rate do it through an iterative process (essentially guessing at a probable number and continuing to guess until the number fits). Bill "George" <g.sova (AT) comcast (DOT) net> wrote in message news:1133111447.388470.263180 (AT) g14g2000cwa (DOT) googlegroups.com... Dear JC: The payoff usually means the remaining balance of the original loan amount.In that case you would use the formula PV(periodic payment, interest rate per period, number of periods). For this formula to work, certain conditions are necessary: 1. each periodic payment is the same; 2. the interest rate is unchanged for the entire period of time; 3. each period is of the same length (usually a month) If all three above conditions are not met, than you have to prepare an amortization schedule for all payments, reflecting in each payment made the amount of payment, the length of the period (the number of days), and the interest charged for the period. It becomes messy, but it would be the only method to compute how much of principal was repaid for the period. George JC wrote: I have the following fields set up. Amount_Financed, Apr, Term, Payment, Payments made What would be the calculation to determine the payoff (doesn't have to be to the penny) Thanks JC "42" <nospam (AT) nospam (DOT) com> wrote in message news:MPG.1dfac1bddab2e698989df3 (AT) shawnews (DOT) vf.shawcable.net... In article <kv5kf.522$MM.388 (AT) fe16 (DOT) usenetserver.com>, michael.myett (AT) adelphia (DOT) net says... JC wrote: Does anyone know the formula for lease calculations, ie. automobile lease for a certain amount of months, knowing the residual, term, and rate factor? Thanks jc They vary slightly from manufacturer or bank. Which lending institution would you are you trying to emulate? is it a true lease with acquisition fees, etc... or just a balloon loan? Which also varies from bank to bank. By the way, it takes a series of calculations and choices. A good lease calculator will have to handle many different senarios, for example, there are upfront fees associated with most leases, which can be paid up front or capitalized, there are often ways to waive acquisition fees or security deposits by adding to the money factor or interest rate. Sometimes the are simply waived as part of a loyalty program provided by the manufacturer. There are other issues regarding sales and property taxes, which are part of the monthly payment in leases, and of course these vary from state to state. If you can provide some additional details, I'm sure I can help. Michael Myett Most the 'variation' you are discussing can be handled with basic arithmetic, the only 'hard' stuff is unravelling the stuff involving compound interest, which Filemaker has built in anyway, at least rudimentarily. I say rudimentarily, because the functions don't support several of the more advanced options you'd find in say Microsoft Excel or Openoffice.org Calc. (stuff like specifying if the payment is made at the beginning or end of the period, or computing the payment of loan with a non-zero principle at the end -- where you'll have some 'extra' compound interest. (But all these options can be implemented manually using the existing functions to do the grunt work if you have an understanding of the underlying math.) |
#8
| |||
| |||
|
|
Actually not, the math behind a lease is much simpler then a standard loan, there is just just more to do and will require your own calculations not any of FileMakers built in functions. Infact, its a very simple procedere using a pencil and paper. There are no odd-days interest because lease payments are paid at the beginning of each period. Lease payments are composed of 2-4 parts, depending on where you live (use tax rate), the lease company and state and federal regulations. 1.) Depreciation (the portion of the value of the vehicle you are using plus any other capitalized costs not paid at inception) 2.) Rent Charge (equivalent of interest) however the monthly rent charge does not vary as it does with a standard loan and is calculated mostly using a money factor instead of an APR. 3.) Use Tax (varies considerably from state to state, most states this tax is added as part of the payment, but not always) NH doesn't have use tax on autos, ME requires tax on the full value of the vehicle to be paid upfront 4.) Property or excise tax (once again, this is handled differently depending on which state you live in. There are also acquisition fees, and security deposits which can be paid at signing, capitalized, or waived in either one of two ways. I was simply suggesting a well designed lease calculator needs to take all these things into consideration. For example, maybe a checkbox which could be checked if the acquisition fee were paid upfront and then based on your design handle the math involved behind the scenes. Michael Myett Bill Marriott wrote: In other words, the article I posted the 27th to this poster's previous question. "Bill Marriott" <wjm (AT) wjm (DOT) org> wrote in message news emdnTjvea9ioxfenZ2dnUVZ_tudnZ2d (AT) comcast (DOT) com...This is an article I wrote many moons ago that might be handy to keep around... Building Your Own Annuity (Financial) Functions You may need to calculate a very specific kind of annuity (financial) function, but the appropriate calculation is not a built-in function in the program you are using. Or, the function used by the program does not include enough arguments to specify all the options you want in the calculation. (For example, all FileMaker Pro financial functions assume that payments/debits are made at the end of a period, not at the beginning.) All financial functions are based on one large, complex formula that can be re-arranged (using standard algebraic techniques) to obtain whatever value you want. Let's look at a formula, and an example of how to derive a function from it. The Master Formula: when rate *<> 0: pv * (1 + rate)^nper + pmt * (1 + rate * type) * (((1 + rate)^nper - 1) / rate) + fv = 0 when rate = 0: pv + pmt * nper + fv = 0 in this formula, pv = the present value of the investment rate = the interest rate each period nper = the number of periods pmt = the payment amount each period type = when payments are made. (0 = end of the period, 1 = beginning of the period.) fv = the future value of the investment Note: Expenses (negative cash flow) are represented by negative numbers. Income (positive cash flow) is represented by positive numbers. For example, pmt is usually a negative number. So, suppose that you wanted to find out the payment required for an investment when the payments are made at the beginning of a period (the FileMaker Pro PMT function works only with payments made at the end of a period.) Here is how you would derive the needed formula: 1. Realize that your end goal is to isolate the PMT variable on one side of the equals sign. You must be able to supply all other variables. Because the interest rate in this case is not zero, we'll use the first formula. 2. PMT is buried inside the formula in a way that does not let us simply subtract it from both sides or divide both sides by it. So we have to move step-by step to simplify the component of the equation that contains PMT. a. Subtract FV from both sides. pv * (1 + rate)^nper + pmt * (1 + rate * type) * (((1 + rate)^nper - 1) / rate) = -fv b. Subtract pv * (1 + rate)^nper from both sides. pmt * (1 + rate * type) * (((1 + rate)^nper - 1) / rate) = -fv - pv * (1 + rate)^nper c. Divide both sides by (1 + rate * type) * (((1 + rate)^nper - 1) / rate) pmt = (-fv - pv * (1 + rate)^nper) / ((1 + rate * type) * (((1 + rate)^nper - 1) / rate)) You can now fill in the values for the variables you know (fv, pv, rate, nper, and type) and FileMaker Pro will calculate the correct payment. Note: Due to the complexity of the equation, the Rate variable cannot be solved by itself with basic algebraic methods. Other programs that do solve for Rate do it through an iterative process (essentially guessing at a probable number and continuing to guess until the number fits). Bill "George" <g.sova (AT) comcast (DOT) net> wrote in message news:1133111447.388470.263180 (AT) g14g2000cwa (DOT) googlegroups.com... Dear JC: The payoff usually means the remaining balance of the original loan amount.In that case you would use the formula PV(periodic payment, interest rate per period, number of periods). For this formula to work, certain conditions are necessary: 1. each periodic payment is the same; 2. the interest rate is unchanged for the entire period of time; 3. each period is of the same length (usually a month) If all three above conditions are not met, than you have to prepare an amortization schedule for all payments, reflecting in each payment made the amount of payment, the length of the period (the number of days), and the interest charged for the period. It becomes messy, but it would be the only method to compute how much of principal was repaid for the period. George JC wrote: I have the following fields set up. Amount_Financed, Apr, Term, Payment, Payments made What would be the calculation to determine the payoff (doesn't have to be to the penny) Thanks JC "42" <nospam (AT) nospam (DOT) com> wrote in message news:MPG.1dfac1bddab2e698989df3 (AT) shawnews (DOT) vf.shawcable.net... In article <kv5kf.522$MM.388 (AT) fe16 (DOT) usenetserver.com>, michael.myett (AT) adelphia (DOT) net says... JC wrote: Does anyone know the formula for lease calculations, ie. automobile lease for a certain amount of months, knowing the residual, term, and rate factor? Thanks jc They vary slightly from manufacturer or bank. Which lending institution would you are you trying to emulate? is it a true lease with acquisition fees, etc... or just a balloon loan? Which also varies from bank to bank. By the way, it takes a series of calculations and choices. A good lease calculator will have to handle many different senarios, for example, there are upfront fees associated with most leases, which can be paid up front or capitalized, there are often ways to waive acquisition fees or security deposits by adding to the money factor or interest rate. Sometimes the are simply waived as part of a loyalty program provided by the manufacturer. There are other issues regarding sales and property taxes, which are part of the monthly payment in leases, and of course these vary from state to state. If you can provide some additional details, I'm sure I can help. Michael Myett Most the 'variation' you are discussing can be handled with basic arithmetic, the only 'hard' stuff is unravelling the stuff involving compound interest, which Filemaker has built in anyway, at least rudimentarily. I say rudimentarily, because the functions don't support several of the more advanced options you'd find in say Microsoft Excel or Openoffice.org Calc. (stuff like specifying if the payment is made at the beginning or end of the period, or computing the payment of loan with a non-zero principle at the end -- where you'll have some 'extra' compound interest. (But all these options can be implemented manually using the existing functions to do the grunt work if you have an understanding of the underlying math.) |
#9
| |||
| |||
|
#10
| |||
| |||
|
|
Thank you all, I think I have most of it, my question now is, does the acquisition fee get added to the top line like the MSRP? Thanks jc You can either add the acquistion fee to the sale price of the vehicle |
tax be paid on the acquistition fee.
Michael Myett
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.