Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

    Does anyone have a function that will calculate the interest rate on a loan when given: Amount Financed, #Months (ie the term of the loan) and the Finance Charge?

    I have tried a number of different approaches including PV2()

    'Present_Value as N = PV2( FinanceCharge as N, Interest_Rate as N, Number_Of_Time_Periods as N )
    'PV2() returns the present value of a stream of Investment s, invested at a fixed Interest_Rate for a certain Number_Of_Time_Periods. The PV() function is used to find the amount that must be invested now to produce a given future amount.

    'The Internal Rate of Return is the interest rate that makes the Net Present Value zero... so I iterate after a first guess

    FUNCTION FinancePercent AS N (FinanceCharge As N, Months As N )
    dim FirstGuess As N
    dim NextGuess As N
    dim PresentValue As N
    dim StopValue As N

    FirstGuess = 0.18 '(ie 18%... the function uses the RATE not the percent)
    NextGuess = 0.18
    PresentValue = FinanceCharge
    StopValue = 0.01 * FinanceCharge

    while abs(PresentValue) > StopValue
    PresentValue = PV2( FinanceCharge, NextGuess, Months )
    'if PresentValue is > 0 then we increase the NextGuess (ie the guessed FinanceRate)
    'if PresentValue is < 0 then we decrease the NextGuess
    if PresentValue > 0 then
    NextGuess = NextGuess + NextGuess * 0.1
    else
    NextGuess = NextGuess - NextGuess * 0.1
    end if
    end while

    FinancePercent = NextGuess * 100

    END FUNCTION

    which doesn't work!

    #2
    Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

    I do that with re-calcing the original until I get there. I call it a roll which is what we did in VB6 years ago.

    Basically:
    You know the finance charge? keep rolling the apr from 0.001 upwards until solve is to much and then roll it back down until you solve the finace charge exactly and you will have the exact apr used.
    This is long and not based on apr, but something else. will give you the idea maybe:
    It is all almost instantaneous using alpha
    make sure you get all the computations into it like sales tax and other that may change.

    Code:
    if var->dm = .F. then
        goto exitdeal
    end if
    if names->group = "cash" .or. names->group = "finance"
    		var->dm = .T.
    	    script_play_local("monform")
    end if	    
    
    if newotd = vfinance then
        goto end
    elseif newotd < vfinance then
    	while newotd<=vfinance
    			parentform:msrp.value = oldmsrp-500
    			var->dm = .T.
    			script_play("monform")
    			if newotd = vfinance
    				exit while
    			end if	
    			oldmsrp = parentform:msrp.value
    			
    end while
    if newotd=vfinance then
    	goto ender
    end if	
    	while newotd>=vfinance 
    		if newotd<=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp+100
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd<=vfinance
    		if newotd>=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp-50
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd>=vfinance 
    		if newotd<=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp+25
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if	
    	while newotd<=vfinance
    		if newotd>=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp-10
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if	
    	while newotd>=vfinance 
    		if newotd<=vfinance 
    			exit while
    		else	
    
    			parentform:msrp.value = oldmsrp+5
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd<=vfinance
    		if newotd>=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp-1		
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd>=vfinance 
    		if newotd<=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp+.10
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd>=vfinance then
    	goto ender
    end if
    	while newotd<=vfinance
    		if newotd>=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp-.5
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd>=vfinance 
    		if newotd<=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp+.1
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd<=vfinance
    		if newotd>=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp-.01
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    	
    '========================================================================================
    
    elseif newotd > vfinance then
    	while newotd>=vfinance 
    		if newotd<=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp+500
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd<=vfinance 
    		if newotd>=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp-100
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd>=vfinance 
    		if newotd<=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp+50
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd<=vfinance 
    		if newotd>=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp-25
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd>=vfinance 
    		if newotd<=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp+5
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd<=vfinance 
    		if newotd>=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp-1
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd>=vfinance 
    		if newotd<=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp+.5
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd<=vfinance 
    		if newotd>=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp-.1
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd=vfinance then
    	goto ender
    end if
    	while newotd>=vfinance 
    		if newotd<=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp+.05
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    if newotd>=vfinance then
    	goto ender
    end if
    	while newotd<=vfinance 
    		if newotd>=vfinance 
    			exit while
    		else	
    			parentform:msrp.value = oldmsrp-.01
    			var->dm = .T.
    			script_play("monform")
    			oldmsrp = parentform:msrp.value
    		end if
    	end while
    	'==============================================================================
    else goto end
    
    end if
    ender:
    
    'script_play("monform")
    end:
    exitdeal:
    Dave Mason
    [email protected]
    Skype is dave.mason46

    Comment


      #3
      Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

      Payment on apr function I had to build to match gvt guidelines for cars. Bear in mind that real estate and automotive computations are a bit different. Days to first is the big problem. Alpha's code is fine but a few pennies off on car loans.

      Code:
      FUNCTION pmtonapr AS N (p as n, r as n, mos as n, type as n, daystofirst as n)
      'Description:p=loan amount,R=rate as 7.0, mos = monthly term, type = type of term, daystofirst=how many days
      'dim pmtonapr AS N 
      'dim p as n = 31912
      'dim r as n = 6.75
      'dim mos as n = 36
      'dim type as n = 3
      'dim df as n
      'dim daystofirst as n = 30
      if r <= 0.00 then
          pmtonapr = p/mos
      else
      dim df as n
      dim rte as n = (r/100)/12
      df = round_UP((daystofirst-30)/30,6)
      dim trm as n = 0
      dim formu as n
      'dim factor as n 
      var->factor = round_up((rte*df)*p,8) 'round_down() does not work in v7
      formu = round_up(p+var->factor,5)
      pmtonapr = round_down(pmts1(formu,r,mos),2)
      
          
      end if
      
      end function
      Last edited by DaveM; 04-13-2016, 07:41 PM.
      Dave Mason
      [email protected]
      Skype is dave.mason46

      Comment


        #4
        Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

        Thanks for the help David...

        I'm not sure that I understand the code that you provide... I think I understand the strategy...
        SO...

        I created the following function...

        FUNCTION FinancePercent2 AS N (FinancedAmt As N, FinanceCharge As N, Months As N, MinMonPay As N )
        dim TotalFinanced As N
        dim CalcPayment As N
        dim NextGuess As N
        dim PaymentAt2Percent As N

        PaymentAt2Percent = payment(FinancedAmt,.02/12,Months)
        if PaymentAt2Percent > MinMonPay then
        ui_msg_box("Payment at 2% is > MinMonPay Entered","Payment at 2% is > MinMonPay Entered... CANNOT CONTINUE",UI_STOP_SYMBOL+UI_OK)
        end
        end if

        CalcPayment = 1
        TotalFinanced = FinancedAmt + FinanceCharge
        NextGuess = .01/12 '(ie 1%... the function uses the RATE/12, if the rate is per year... it doesn't use the % value)
        ui_msg_box("NextGuess = " + alltrim(str(NextGuess,18,4)),"NextGuess = " + alltrim(str(NextGuess,18,4)) + " TotalFinanced = " + alltrim(str(TotalFinanced)),UI_INFORMATION_SYMBOL+UI_OK)
        ui_msg_box("Months = " + alltrim(str(Months,18,4)),"Months = " + alltrim(str(Months,18,4)) + " MinMonPay = " + alltrim(str(MinMonPay)),UI_INFORMATION_SYMBOL+UI_OK)
        'end
        while CalcPayment < MinMonPay
        CalcPayment = Payment( TotalFinanced, NextGuess, Months )
        'ui_msg_box("CalcPay = " + alltrim(str(CalcPayment)) + " Rate = " + alltrim(str(NextGuess,18,4)),"CalcPay = " + alltrim(str(CalcPayment)) + " Rate = " + alltrim(str(NextGuess,18,4)),UI_INFORMATION_SYMBOL+UI_OK)
        NextGuess = NextGuess + 0.0001
        end while

        FinancePercent2 = NextGuess * 100

        END FUNCTION

        BUT.. it doesnt work!... so I am still not understanding something...
        advice?

        Comment


          #5
          Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

          Are you trying to figure payments accurately, or use something else?
          I must not be understanding what you are doing.

          To get payments normally, you would have finamount, term, apr
          The other stuff I put in there is for car stuff mostly.
          "Type" may be buyherepayhere or other
          "days to first" is needed in car loans
          The function will give a payment - to get full balance = add all the payments - to get interest subtract the loan amount

          If you email me I will send you a movie of the action with roll and maybe more so you can see.
          Dave Mason
          [email protected]
          Skype is dave.mason46

          Comment


            #6
            Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

            I want to calculate the interest rate...
            A salesman has entered a Financed Amount, Finance Charge, Term and minimum monthly payment. He does not know what the interest rate would have to be to produce the above values on the contract.
            I need to 'back calculate' the interest rate on the loan so that the alpha database will contain accurate numbers.

            Comment


              #7
              Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

              That is a roll situation using the tool from which the payment was derived.
              need to know:
              simple or rule of 78 or CC or other

              Go back to what I first sent up top for a roll
              It is not a simple function, but rather a payment function and a script to do the roll. The script will call the pament function a number of times during the roll.
              use vars with finamnt, fincharge, term and payment the

              When you threw in MINIMUM payment, it sounds like a CC type loan and uses completely different calculations which I have never done. That does not mix well with "term".
              Dave Mason
              [email protected]
              Skype is dave.mason46

              Comment


                #8
                Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

                This may not be what you want, but could be close
                Cautions: The tool used to do original calc could have different apr roundings like 6 decimals or 8. Any bank fees added to the interest can affect the final apr and is where you can get 2 apr's stated on paperwork and these are NOT added to the amount financed. Some county and state fees may not be supervisible that could cause a difference in actual amount financed.

                Code:
                dim financed as n = 30000
                dim fincharge as n  = 6000
                dim pmts as n = 3  'years
                dim thefincharge as n
                dim finapr as n = .05 like 5.0/100 
                dim payt as n
                
                while thefincharge < fincharge
                finapr = finapr + .00001
                payt = payment(var->financed,(var->finapr/12),(var->pmts*12))  'using built in alpha payment method
                thefincharge = (payt*(pmts*12))-financed
                end while
                
                ?payt
                = 1000.00087871321
                
                
                
                ?thefincharge
                = 6000.03163367546
                
                
                ?finapr*100
                = 12.2489999999981
                Last edited by DaveM; 04-15-2016, 03:21 PM.
                Dave Mason
                [email protected]
                Skype is dave.mason46

                Comment


                  #9
                  Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

                  Originally posted by David Boomer View Post
                  I want to calculate the interest rate...
                  A salesman has entered a Financed Amount, Finance Charge, Term and minimum monthly payment. He does not know what the interest rate would have to be to produce the above values on the contract.
                  I need to 'back calculate' the interest rate on the loan so that the alpha database will contain accurate numbers.

                  David,

                  I have hesitated to jump into this, but you don't seem to be getting what you want, so....

                  I think you need to be clearer about what you are trying to do. Dave Mason is right, it matters as to what kind of loan you are talking about, at least if an APR is what you are actually after (which is not clear). I am sure car loans are different from home mortgages. And if, as Dave Mason seemed to assume, you are after an APR rather than a simple note rate, there are all kinds of definitions of what an APR is, depending on the legal jurisdiction, as it has to do with truth in lending rather than something used to calculate a payment schedule. For home mortgages in the US the Note Rate (say a "4% loan") will often if not always be somewhat lower than the APR because the APR must factor in fees and other costs that may be required to get a 4% Note Rate loan. The 4% Note Rate would be used to calculate the payment, but the true cost for truth in lending purposes would usually be somewhat more than 4%.

                  Now, if you are after the APR (and maybe even if you are trying to back in to a simple Note Rate) you need to understand that there is no such thing a simple mathematical formula that will calculate an APR for a US mortgage, or I believe, to backward calculate a simple note rate the way you may be wanting to do. For APR, if you use the usual HP calculator used by many mortgage brokers to calculate an APR, you'll note that there is a slight delay in getting an answer. The calculator is churning away at numerous approximations until it gets one that is close enough (your computer will be doing the same, though it usually won't be noticeable). Note the "close enough", i.e., there is no such thing as the 100% right answer down to such and such decimal point using such methods.

                  Also, it seems to me that you should give an example to help define the terms you are using. Is "Financed Amount" the amount of the loan whereas "Finance Charge" is total of all interest that will have been paid at the end of the loan Term, assuming constant "minimum monthly payments" over the entire term? If not, what do these terms mean? Would there be other costs involve that are not included in the "Finance Charge", and if so can they be ignored? Is this a home mortgage, a car loan or what? Whatever answer a function may come up with, how closely must it match what some official answer might specify? And do you have that official answer for an example? Anyway, give us a real example of some real numbers to work with, especially if you know what would count as the right (or close to right) answer.

                  Finally, I would love to be wrong, i.e., that someone out there has a simple mathematical formula that will produce a 100% right APR answer.

                  Ray Lyons

                  Comment


                    #10
                    Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

                    Adding to Ray's help.

                    The federal governmemt puts out a manual called "Regulation Z" that is the federal guidline for all loans in the US for anything. Most States have their own Manual of how they interpret that book.

                    It is all very confusing.

                    I have only been involved in auto/boat/etc. loans and found I had to write my own calcs and used a countrywide competitor as a guide and matched it to the penny. Using others, I may be a penny off, but all are usually within rounding rules which makes them ok.

                    From what I have seen, the alpha payments will match very closely to home loans. When doing car loans with first payment dates being in the future instead of the same day, it will not work. That is why you can find days to first in my function.

                    A 36 month payment may be 36 mos+15 to 60 extra days. It took me a long time to get this one somewhat simple looking function to be exact to the penny.

                    another thought is the legal maximum annual pct rates
                    Last I checked:
                    a car is 17% if new and/or up to 2 model years old
                    Florida is 27% on older, but other states do vary so the lender needs to be aware of what the rate max is where they are.
                    Last edited by DaveM; 04-15-2016, 08:17 PM.
                    Dave Mason
                    [email protected]
                    Skype is dave.mason46

                    Comment


                      #11
                      Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

                      Thanks for the advice... it helped me come up with the following 'solution'

                      To frame my problem:
                      Payment() is an Alpha function that yields the required minimum payment on a loan:
                      Here is an example:
                      ?payment(3330,.24/12,18) Amount Financed = $3330.00 interest rate = 0.24/12 (ie 24% per annum) the term is 18 months
                      = 222.118000162134

                      Ok this tells me that the minimum monthly payment should be $220 on this loan
                      BUT... the sales people in the field make 'convenient' contracts with the customers and often do not conform strictly with the Finance tables... and do not enter the actual % that they used to calculate (in fact they often do not calculate, they just enter 'agreed upon values'
                      So, for example the salesperson my enter $3000 as the amount borrowed, $330 as the finance charge, 18 months as the term and a minimum monthly payment of $200
                      Obviously this can create BIG PROBLEMS for my database!
                      So I need a function that takes the information that was provided by the consultant and A) indicates if the calculation of percent can logically be carried out (given the supplied minimum monthly payment etc) and B) the actual percent...
                      Here is what worked...
                      'This function estimates the Finance Percent given the Principle, the FinanceCharge, the #Months (ie Term) and MinMonPay
                      'David Boomer April 2016

                      'The key is to make a guess for the InterestRate... then iterate through the calculation for PaymentAmount
                      'by incrementing the MinMonPay until it ~ $MinMonPayReq'd
                      '{Could also be done by doing the same with FinanceCharge}

                      'Payment_Amount as N = PAYMENT( Loan_Amount as N, Interest_Rate as N, Number_Of_Time_Periods as N )
                      'eg Payment(1000,.24/12,18) = $66.702 = MinMonPay

                      'For example:
                      'A guess of 6% Finance% on a Financed Amount of $3000 over 18 months Payment(3000,.06/12,18) yields a payment of $174.69
                      '
                      'But the contract called for a MinMonPay Value of $200.00, so we try 24% interest:
                      'At 24% we get a MinMonPay Value of $200.106 which is pretty close...
                      'the computer can do a better job of guessing!

                      FUNCTION FinancePercent AS N (FinancedAmt As N, FinanceCharge As N, Months As N, MinMonPay As N )
                      dim TotalFinanced As N
                      dim CalcPayment As N
                      dim NextGuess As N
                      dim PaymentAt2Percent As N
                      dim PaymentAt50Percent As N

                      ui_msg_box("REMEMBER... There is only a narrow range of valid MinMonPay","REMEMBER... There is only a narrow range of valid MinMonPay that will satisfy values for Financed Amount, Financecharge & Term",UI_INFORMATION_SYMBOL+UI_OK)

                      PaymentAt2Percent = payment(FinancedAmt,.02/12,Months)
                      if PaymentAt2Percent > MinMonPay then
                      Response = ui_msg_box("Payment at 2% is > MinMonPay Entered","Payment at 2% is > MinMonPay Entered... DO NOT CONTINUE",UI_INFORMATION_SYMBOL+UI_OK_CANCEL)
                      if Response = UI_CANCEL_SELECTED then
                      end
                      end if
                      end if

                      PaymentAt50Percent = payment(FinancedAmt,.5/12,Months)
                      if PaymentAt50Percent < MinMonPay then
                      Response = ui_msg_box("Payment at 50% is still < MinMonPay Entered","Payment at 50% is still < MinMonPay Entered... DO NOT CONTINUE",UI_INFORMATION_SYMBOL+UI_OK_CANCEL)
                      if Response = UI_CANCEL_SELECTED then
                      end
                      end if
                      end if

                      Response = ui_msg_box("MinMonPay At 2% = " + alltrim(str(PaymentAt2Percent,18,2)) + " MinMonPay At 50 % = " + alltrim(str(PaymentAt50Percent,18,2)),"MinMonPay At 2% = " + alltrim(str(PaymentAt2Percent,18,2))+ " MinMonPay At 50 % = " + alltrim(str(PaymentAt50Percent,18,2)),UI_INFORMATION_SYMBOL+UI_OK_CANCEL)
                      if Response = UI_CANCEL_SELECTED then
                      end
                      end if

                      CalcPayment = 1
                      TotalFinanced = FinancedAmt + FinanceCharge
                      NextGuess = .01/12 '(ie 1%... the function uses the RATE/12, if the rate is per year... it doesn't use the % value)
                      'ui_msg_box("NextGuess = " + alltrim(str(NextGuess,18,4)),"NextGuess = " + alltrim(str(NextGuess,18,4)) + " TotalFinanced = " + alltrim(str(TotalFinanced)),UI_INFORMATION_SYMBOL+UI_OK)
                      'ui_msg_box("Months = " + alltrim(str(Months,18,4)),"Months = " + alltrim(str(Months,18,4)) + " MinMonPay = " + alltrim(str(MinMonPay)),UI_INFORMATION_SYMBOL+UI_OK)
                      'end
                      DIM GLOBAL pDlg1 as waitdialog
                      pDlg1.Set_Title("Please Wait")
                      pDlg1.Create(1,"Repeating")
                      pDlg1.Set_Message("Calculating Finance %...")
                      pDlg1.Set_Bottom_Message("")
                      while CalcPayment < MinMonPay
                      CalcPayment = Payment( TotalFinanced, NextGuess, Months )
                      'ui_msg_box("CalcPay = " + alltrim(str(CalcPayment)) + " Rate = " + alltrim(str(NextGuess,18,4)),"CalcPay = " + alltrim(str(CalcPayment)) + " Rate = " + alltrim(str(NextGuess,18,4)),UI_INFORMATION_SYMBOL+UI_OK)
                      NextGuess = NextGuess + 0.0000001
                      end while
                      pDlg1.Close()
                      FinancePercent = round(NextGuess * 1200,2)

                      END FUNCTION

                      As proof of success I offer:
                      ?FinancePercent(3000,330,18,222)
                      = 23.93

                      ?payment(3330,.24/12,18)
                      = 222.118000162134

                      Thanks for your help... I wouldn't have got here this fast without it.

                      Comment


                        #12
                        Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

                        So, for example the salesperson my enter $3000 as the amount borrowed, $330 as the finance charge, 18 months as the term and a minimum monthly payment of $200
                        Holy cow! Is this legal in Canada? Do you really mean to say that someone agrees to borrow and someone agrees to lend $3,000, and what the lender wants back is $3,330 after 18 months at no less than $200 per month?

                        Gee, that would mean lender gets back $3,600 instead of $3,330. Or the term isn't really 18 months but actually 16.65--or less if they paid more than $200 in some months. And the payment in your calculation is not on the $3,000 borrowed but on that plus the total amount in interest payments the lender expects to get at the end? All I can say is, I find this totally bizarre.

                        Comment


                          #13
                          Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

                          This kind of calculation and dealing in FL could land somebody in Jail. All the calcs would be considered wrong and setting the amount of interest to a client at time of sale without apr disclosure causes a NON-Contract and possible penalties.
                          To the division of Banking, Finance and Insurance, the most important thing in a contract of any kind is the rates disclosures. Everything else is based off that.
                          Dave Mason
                          [email protected]
                          Skype is dave.mason46

                          Comment


                            #14
                            Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

                            Yes.. I know it's bizarre...
                            The function is needed primarily because salespeople are often working in remote areas and sometimes forget their loan interest tables.... so they guess the finance charge and minimum monthly payment based on past experience... when the order is processed back at the main office it becomes necessary to try to honour the 'contract' that the customer has a copy of... the percent is back calculated so that the history of the order is as accurate as possible.

                            Comment


                              #15
                              Re: Financial Calculation (Interest Rate given Amt Financed, Months, FinanceCharge)

                              I don't know if it will help, but here is an example of how to do it in an Excel spreadsheet wikihowto
                              Robin

                              Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                              Comment

                              Working...
                              X