{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Quotation","Finance Quote","CBA","Calculations","Workings (A)","Workings (B)","Workings (C)"],"not_rendered":{"charts":1,"images":1,"macros":1,"external_links":0,"defined_names":14,"comments":5,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Quotation","index":0,"n_rows":51,"n_cols":22,"rows_kept":51,"cut":false,"hidden":false,"merged":[[50,1,50,16],[30,1,30,9],[18,5,18,11],[13,2,13,4],[14,2,14,4],[15,2,15,4],[22,2,22,4],[21,2,21,4],[27,2,27,4],[25,2,25,4],[24,2,24,4]]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"APR Calculator",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Quotation Model"]
[null,null,"Scenario 1","Scenario 2","Scenario 3"]
[null,"Broker Name:","[Broker Name]",null,null,null,null,null,null,null,null,null,null,null,null,null,"Total Amount Payable:",null,null,null,{"$f":"IF(U15=C20,'Workings (A)'!C22,IF(U15=D20,'Workings (B)'!C22,'Workings (C)'!C22))","v":1039545}]
[null,"Client Name:","[Client Name]",null,null,null,null,null,null,null,null,null,null,null,null,null,"Number of  Payments:***",null,null,null,10,"Please input the No of payments"]
[null,"Initial Transaction Amount:",1000000,null,null,null,null,null,null,null,null,null,null,null,null,null,"Fee Amount:",null,null,null,{"$f":"IF(U15=C20,'Workings (A)'!C17,IF(U15=D20,'Workings (B)'!C17,'Workings (C)'!C17))","v":45},"Collected with the initial payment"]
[null,"Facility Fee:","Yes","Yes","Yes","Please select if fee applies",null,null,null,null,null,null,null,null,null,null,"First payment amount collected by broker",null,null,null,0]
[null,"Transaction Fee",2.4E-2,3.2000000000000001E-2,3.95E-2,null,null,null,null,null,null,null,null,null,null,null,"Initial Direct Debit of:",null,null,null,{"$f":"IF(U15=C20,IF(U17=0,'Workings (A)'!C24,U19+'Workings (A)'!C17),IF(U15=D20,IF(U17=0,'Workings (B)'!C24,U19+'Workings (B)'!C17),IF(U17=0,'Workings (C)'!C24,U19+'Workings (C)'!C17)))","v":103995}]
[null,"Minimum Transaction Fee *",25,25,25,"* Please input the amount",null,null,null,null,null,null,null,null,null,null,"Followed by",{"$f":"IF(U17=0,(U15-1),U15-2)","v":9},"payments of:",null,{"$f":"IF(U15=C20,IF(U17=0,('Workings (A)'!C22-'Workings (A)'!C17)/U15,('Workings (A)'!C22-'Workings (A)'!C17-U17)/(U15-1)),IF(U15=E20,IF(U17=0,('Workings (C)'!C22-'Workings (C)'!C17)/U15,('Workings (C)'!C22-'Workings (C)'!C17-U17)/(U15-1)),IF(U17=0,('Workings (B)'!C22-'Workings (B)'!C17)/U15,('Workings (B)'!C22-'Workings (B)'!C17-U17)/(U15-1))))","v":103950}]
[null,"Instalment Term",6,8,10,null,null,null,null,null,null,null,null,null,null,null,"Anticipated Credit Limit:",null,null,null,{"$f":"IF(U15=C20,'Workings (A)'!C23,IF(U15=D20,'Workings (B)'!C23,'Workings (C)'!C23))","v":1039545}]
[]
[null,"Corporation Tax Rate:",19,null,null,"%",null,null,null,null,null,null,null,null,null,null,{"$f":"\"*** This is the total number of monthly instalments, including the first payment amount, which may have been collected by \"&C14","v":"*** This is the total number of monthly instalments, including the first payment amount, which may have been collected by [Broker Name]"}]
[null,"Client ROCE:**",10,null,null,"%"]
[]
[null,"Show Corporation Tax","Yes"]
[null,"Show APR","No"]
[null,"APR",{"$f":"'Workings (A)'!B28","v":4.4999999999999998E-2},{"$f":"'Workings (B)'!B28","v":0.06},{"$f":"'Workings (C)'!B28","v":7.4999999999999997E-2},"   APR Variable"]
[null,"Currency","£"]
[]
[null,"This APR is calculated on the assumptions that:"]
[null,"• The credit agreement remains valid for the period agreed and that the parties to the credit agreement fulfil their obligations under the terms and by the dates specified in the agreement;",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Overall cashflow position",null,null,{"$f":"Calculations!T26","v":0}]
[null,"• The rate of the transaction fee will remain at the initial level and will be applicable for the duration of the agreement;",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"A positive figure indicates a favourable cashflow position by using premium finance"]
[null,"• The Anticipated Credit Limit is drawn down immediately and in full;"]
[null,"• The credit is provided for a period of one year starting from the date of the initial drawdown, the capital is repaid in  equal 12"]
[null,"monthly payments commencing one month after the date of the initial drawdown, the final payment made by the borrower clears the"]
[null,"balance of capital and charges, and that charges are applied in accordance with that drawdown and repayments of capital and as"]
[null,"provided for in the consumer credit agreement."]
[null,"• The facility fee is collected in full with the first payment"]
[]
[]
[]
[null,"Important - Please note"]
[null,"1. This calculation is for illustration purposes only. "]
[null,"2. Premium Credit Limited accepts no liability for decisions made or actions taken based on this calculation."]
[null,"3 This tool is intended to assist you in the production of personalised quotations for your client provided by you not Premium Credit Ltd"]
[null,"4 This tool is not for advertising and should not be used to calculate a representative example within the meaning of the FCA rules on financial promotions and customer communications"]
[null,"5. You should make clear that this quotation is for information purposes only and does not constitute an offer or commitment on the part of Premium Credit Ltd nor does it bind Premium Credit Ltd in any way to provide credit or enter into a credit agreement"]
[null,"6. Credit is subject to status."]
[null,"7.  The terms of any credit agreement will be as set out in that document and may differ from those in this quotation.  For example the APR, the transaction fee and the credit limit for any individual customer may differ from those set out above"]
[null,"8.  You are responsible for complying with all rules and regulations in relation to financial promotions"]
[null,"9. For more information regarding current corporation tax rates, please visit https://www.gov.uk/government/publications/rates-and-allowances-corporation-tax/rates-and-allowances-corporation-tax"]

{"sheet":"Finance Quote","index":1,"n_rows":47,"n_cols":7,"rows_kept":47,"cut":false,"hidden":false,"merged":[[11,0,11,3],[12,0,12,3],[12,4,12,7],[32,6,33,6],[35,1,36,3],[35,4,36,4],[35,5,36,5],[35,6,36,6],[27,1,29,3],[27,4,29,4],[27,5,29,5],[11,4,11,7],[15,1,15,3],[17,1,18,3],[17,4,18,4],[17,5,18,5],[52,0,52,7],[53,0,53,7],[32,1,33,3],[32,4,33,4],[32,5,33,5],[42,0,43,7],[46,0,47,7],[17,6,18,6],[19,1,20,3],[19,4,20,4],[19,6,20,6],[19,5,20,5],[27,6,29,6],[26,1,26,3],[21,6,22,6],[23,5,24,5],[23,6,24,6],[21,1,22,3],[23,1,24,3],[21,4,22,4],[23,4,24,4],[21,5,22,5]]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Finance Quotation"]
[]
["Broker:",null,null,null,{"$f":"Quotation!C14","v":"[Broker Name]"}]
["Analysis for:","Analysis for:",{"$f":"Quotation!L15","v":0},{"$f":"Quotation!P10","v":0},{"$f":"Quotation!C15","v":"[Client Name]"}]
[]
[]
[null,"Number of Payments",null,null,{"$f":"IF(Quotation!C20=0,\"x\",Quotation!C20)","v":6},{"$f":"IF(Quotation!D20=0,\"x\",Quotation!D20)","v":8},{"$f":"IF(Quotation!E20=0,\"x\",Quotation!E20)","v":10}]
[]
[null,"Initial Premium",null,null,{"$f":"IF($E$16=\"x\",\"x\",Quotation!C16)","v":1000000},{"$f":"IF($F$16=\"x\",\"x\",Quotation!C16)","v":1000000},{"$f":"IF($G$16=\"x\",\"x\",Quotation!C16)","v":1000000}]
[]
[null,"Transaction Charge",null,null,{"$f":"IF($E$16=\"x\",\"x\",'Workings (A)'!C19/E18)","v":2.4E-2},{"$f":"IF($F$16=\"x\",\"x\",'Workings (B)'!C19/F18)","v":3.2000000000000001E-2},{"$f":"IF($G$16=\"x\",\"x\",'Workings (C)'!C19/G18)","v":3.95E-2}]
[]
[null,"Facility Fee",null,null,{"$f":"IF($E$16=\"x\",\"x\",'Workings (A)'!C17)","v":45},{"$f":"IF($F$16=\"x\",\"x\",'Workings (B)'!C17)","v":45},{"$f":"IF($G$16=\"x\",\"x\",'Workings (C)'!C17)","v":45}]
[]
[null,"Total Amount Payable",null,null,{"$f":"IF($E$16=\"x\",\"x\",'Workings (A)'!C22)","v":1024045},{"$f":"IF($F$16=\"x\",\"x\",'Workings (B)'!C22)","v":1032045},{"$f":"IF($G$16=\"x\",\"x\",'Workings (C)'!C22)","v":1039545}]
[]
[]
[null,"Initial Payment",null,null,{"$f":"IF($E$16=\"x\",\"x\",'Workings (A)'!C24)","v":170711.66666666666},{"$f":"IF($F$16=\"x\",\"x\",'Workings (B)'!C24)","v":129045},{"$f":"IF($G$16=\"x\",\"x\",'Workings (C)'!C24)","v":103995}]
[null,"Remaining Payments of",null,null,{"$f":"IF($E$16=\"x\",\"x\",'Workings (A)'!C25)","v":170666.66666666666},{"$f":"IF($F$16=\"x\",\"x\",'Workings (B)'!C25)","v":129000},{"$f":"IF($G$16=\"x\",\"x\",'Workings (C)'!C25)","v":103950}]
[]
[]
[]
[]
[null,"APR",null,null,{"$f":"IF($E$16=\"x\",\"x\",'Workings (A)'!B28)","v":4.4999999999999998E-2},{"$f":"IF($F$16=\"x\",\"x\",'Workings (B)'!B28)","v":0.06},{"$f":"IF($G$16=\"x\",\"x\",'Workings (C)'!B28)","v":7.4999999999999997E-2}]
[]
[]
[null,"Corporation Tax Savings",null,null,{"$f":"IF(E16=\"x\",\"x\",E20*E18*CBA!$C$23/100)","v":4560},{"$f":"IF(F16=\"x\",\"x\",F20*F18*CBA!$C$23/100)","v":6080},{"$f":"IF(G16=\"x\",\"x\",G20*G18*CBA!$C$23/100)","v":7505}]
[]
[]
[]
[]
[]
["Important - Please note   "]
["1. This calculation is for illustration purposes only and does not constitute an offer or commitment on the part of Premium Credit Ltd nor does it bind Premium Credit Ltd in any way to provide credit or enter into a Credit Agreement."]
[]
["2. Premium Credit Limited accepts no liability for decisions made or actions taken based on this calculation. "]
["3. Credit is subject to status.               "]
["4. The terms of any Credit Agreement will be as set out in that document and may differ from those in this quotation. For example the transaction fee and the credit limit for any individual customer may differ from those set out above. "]

{"sheet":"CBA","index":2,"n_rows":56,"n_cols":8,"rows_kept":56,"cut":false,"hidden":false,"merged":[[15,1,15,7],[9,1,9,2],[11,3,11,7],[12,3,12,7],[48,1,48,7],[50,1,50,7],[52,1,52,7],[55,1,55,2],[27,4,27,6],[28,1,28,7],[30,1,30,7],[27,1,27,2]]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Cost Benefit Analysis"]
[]
[null,null,"Broker:",{"$f":"Quotation!C14","v":"[Broker Name]"},{"$f":"Quotation!G9","v":0},{"$f":"Quotation!H9","v":0},{"$f":"Quotation!I9","v":0},{"$f":"Quotation!J9","v":0}]
[null,null,"Analysis for:",{"$f":"Quotation!C15","v":"[Client Name]"},{"$f":"Quotation!G10","v":0},{"$f":"Quotation!H10","v":0},{"$f":"Quotation!I10","v":0},{"$f":"Quotation!J10","v":0}]
[]
[]
[null,"Using both the return on capital employed percentage together with the additional saving to corporation tax this analysis demonstrates the effect on your cashflow position by using premium finance instead of paying the full premium upfront from cash."]
[]
[]
[null,"Premium Amount:",{"$f":"Quotation!C16","v":1000000},null,"Total Amount Payable:",null,null,{"$f":"Quotation!U14","v":1039545}]
[null,"Monthly Instalments:* ",{"$f":"Quotation!U15","v":10},null,"Initial Direct Debit of:",null,null,{"$f":"Quotation!U18","v":103995}]
[null,"Transaction Charge",{"$f":"HLOOKUP(C20,'Finance Quote'!E16:G21,5,FALSE)","v":3.95E-2},null,"Followed by",{"$f":"Quotation!R19","v":9},"payments of:",{"$f":"Quotation!U19","v":103950}]
[null,"Corporation Tax Savings",{"$f":"HLOOKUP(C20,'Finance Quote'!E16:G38,21,FALSE)","v":7505}]
[null,"Corporation Tax Rate:",{"$f":"Quotation!C22","v":19},"%"]
[null,"Client ROCE:**",{"$f":"Quotation!C23","v":10},"%"]
[null,"APR",{"$f":"HLOOKUP(C20,Quotation!C20:E27,8,FALSE)","v":7.4999999999999997E-2}]
[]
[]
[null,"Overall cashflow position",null,null,{"$f":"Calculations!T26","v":0}]
[null,"This positive figure indicates a favourable cashflow position by using premium finance"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Assumptions/Notes"]
[null,"1. The loan is repaid by monthly instalments from the date the insurance policy begins "]
[null,"2. The example shows the effects of using premium finance to enable capital investment by the client at their ROCE rate"]
[null,"3. The cashflows based on the Premium and Interest Rate above are calculated (after tax is deducted) and is compared with the cashflow "]
[null,"  that could be generated if the annual premium was reinvested into the business from the date the policy began"]
[null,"4. This calculation should only be used for non Consumer Credit Act business with premiums over £3,500"]
[null,"5. This calculation is for illustration purposes only. "]
[null,"6. Premium Credit Limited accepts no liability for decisions made or actions taken based on this calculation."]
[null,"7. Terms and conditions apply. Credit is Subject to status"]
[null,"*This is the total number of instalments including the initial instalment"]
[null,"** Return on Capital Employed (ROCE) %"]

{"sheet":"Calculations","index":3,"n_rows":84,"n_cols":20,"rows_kept":84,"cut":false,"hidden":true,"merged":[[9,11,10,11],[9,12,10,12],[9,10,10,10],[9,15,10,15],[9,13,10,13],[9,21,10,21],[9,17,10,17],[9,18,10,18],[9,19,10,19],[9,14,10,14],[9,16,10,16]]}
[" ",null,null,null,null,null,null,null,null,null,null,"CALCULATION OF APR % ( Annual Percentage Rate of Charge)"]
[]
[" ",null,null,null,"Deposit",{"$f":"Quotation!U17","v":0}]
[null,"PREMIUM",{"$f":"Quotation!C16","v":1000000},null,"Initial Inst",{"$f":"Quotation!U18","v":103995}]
[null,null,null,null,"Followed by",{"$f":"Quotation!U19","v":103950},"Input Flat % and Term to obtain Apr%"]
[null,"SERVICE CHARGE",{"$f":"HLOOKUP(C8,CBA!C20:C21,2,FALSE)*C4","v":39500},null,"Flat rate %",{"$f":"C6/C4","v":3.95E-2}]
[]
[null,"No OF INSTALMENTS",{"$f":"Quotation!U15","v":10},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Client ROCE","Corp Tax %"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Quotation!C23","v":10},{"$f":"Quotation!C22","v":19}]
[null,"ESTIMATED EIR",10.938630859591354,null,null,null,null,null,null,null,"Month","Instalments paid in advance","Loan Balance Remaining","Service Charge Element","Premium finance service\ncharge (interest) net of tax","Capital Portion Paid Off","Investment Balance Remaining","Return On Capital Employed\n(Net of tax)","Tax Deducted","Profit/(Loss)"]
[]
[null,null,null,null,null,null,null,null,null,null,1,{"$f":"IF(F3>0,F3,IF($F$4>0,$F$4,F5))","v":103995},{"$f":"C4-L12","v":896005},{"$f":"M12*$C$10/100/12","v":8167.5566194567919},{"$f":"N12-S12","v":6615.7208617600018},{"$f":"L12-N12","v":95827.443380543205},{"$f":"M12","v":896005},{"$f":"Q12*($R$9/100)/12","v":7466.708333333333},{"$f":"N12*$S$9/100","v":1551.8357576967903},{"$f":"R12-(N12-S12)","v":850.98747157333128}]
[null,null,null,null,null,null,null,null,null,null,2,{"$f":"IF(F3>0,F4,IF($C$8>=2,$F$5,0))","v":103950},{"$f":"M12+N12-L13","v":800222.55661945674},7294.4492936489005,5908.5039278556096,96655.550706351103,{"$f":"IF(L13<=0,0,(+Q12-L13+R12))","v":799521.70833333337},6662.6809027777781,{"$f":"N13*$S$9/100","v":1385.9453657932911},754.17697492216848]
[null,null,null,null,null,null,null,null,null,null,3,{"$f":"IF($C$8>=$K14,$F$5,0)","v":103950},{"$f":"M13+N13-L14","v":703567.00591310568},6413.3831355594921,5194.8403398031887,97536.616864440512,702234.38923611119,5851.9532436342597,{"$f":"N14*$S$9/100","v":1218.5427957563036},657.11290383107098]
[null,null,null,null,null,null,null,null,null,null,4,103950,{"$f":"M14+N14-L15","v":606030.38904866518},5524.2855962482354,4474.6713329610702,98425.714403751772,604136.34247974551,5034.4695206645456,{"$f":"N15*$S$9/100","v":1049.6142632871647},559.79818770347538]
[null,null,null,null,null,null,null,null,null,null,5,103950,{"$f":"IF(L16<=0,0,(+M15+N15-L16))","v":507604.67464491341},4627.0834654530654,3747.9376070169828,99322.916534546937,505220.81200041005,4210.1734333367503,879.14585843608245,462.23582631976751]
[null,null,null,null,null,null,null,null,null,null,6,103950,408281.75811036647,3721.7028655618892,3014.5793211051305,100228.29713443811,405480.98543374683,3379.0082119478902,707.12354445675896,364.42889084275976]
[null,null,null,null,null,null,null,null,null,null,7,103950,308053.46097592835,2808.0692455293424,2274.5360888787673,101141.93075447065,304909.99364569469,2540.9166137141224,533.53315665057505,266.38052483535512]
[null,null,null,null,null,null,null,null,null,null,8,103950,206911.53022145771,1886.1073747380888,1527.7469735378518,102063.89262526191,203500.91025940882,1695.840918828407,358.36040120023688,168.09394529055521]
[null,null,null,null,null,null,null,null,null,null,9,103950,104847.6375961958,955.74133680416514,774.15048281137376,102994.25866319584,101246.75117823723,843.72292648531038,181.59085399279138,69.572443673936618]
[null,null,null,null,null,null,null,null,null,null,10,103950,1853.3789329999563,16.894523325858184,13.684563893945128,103933.10547667414,-1859.5258952774584,-15.496049127312155,3.2099594319130551,-29.180613021257283]
[null,null,null,null,null,null,null,null,null,null,11,0,0,0,0,0,0,0,0,0]
[null,null,null,null,null,null,null,null,null,null,12,0,0,0,0,0,0,0,0,0]
[null,null,null,null,null,null,null,null,null,null,13]
[null,null,null,null,null,null,null,null,null,null,"-","-","-","-","-","-","-","-","-","-"]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(L12:L25)","v":1039545},null,{"$f":"SUM(N12:N25)","v":41415.273456325827},{"$f":"SUM(O12:O25)","v":33546.371499623921},{"$f":"SUM(P12:P25)","v":998129.72654367425},null,{"$f":"SUM(R12:R25)","v":37669.978055595086},{"$f":"SUM(S12:S25)","v":7868.9019567019077},{"$f":"IF(ROUND(P84,0)<>0,0,SUM(T12:T25))","v":0}]
[null,null,null,null,null,null,null,null,null,null,"=","=","=","=","=","=","=","=","=","="]
[]
[]
[null,null,null,null,null,null,null,null,null,null,13,{"$f":"IF($C$8>=$K30,$F$5,0)","v":0},{"$f":"IF(L30<=0,0,(+M29+N29-L30))","v":0},{"$f":"M30*$C$10/100/12","v":0},null,{"$f":"L30-N30","v":0}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"K30+1","v":14},0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,15,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,16,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,17,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,18,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,19,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,20,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,21,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,22,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,23,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,24,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,25,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,26,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,27,0,{"$f":"IF(L44<=0,0,(+M43+N43-L44))","v":0},0,null,0]
[null,null,null,null,null,null,null,null,null,null,28,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,29,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,30,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,31,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,32,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,33,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,34,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,35,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,36,0,0,0,null,0]
[]
[null,null,null,null,null,null,null,null,null,null,"-","-","-","-",null,"-"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(P30:P54)+P26","v":998129.72654367425}]
[null,null,null,null,null,null,null,null,null,null,"=","=","=","=",null,"="]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"P56-C4","v":-1870.2734563257545}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"K53+1","v":37},{"$f":"IF($C$8>=$K59,$F$5,0)","v":0},{"$f":"IF(L59<=0,0,(+M53+N53-L59))","v":0},{"$f":"M59*$C$10/100/12","v":0},null,{"$f":"L59-N59","v":0}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"K59+1","v":38},0,{"$f":"IF(L60<=0,0,(+M59+N59-L60))","v":0},0,null,0]
[null,null,null,null,null,null,null,null,null,null,39,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,40,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,41,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,42,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,43,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,44,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,45,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,46,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,47,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,48,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,49,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,50,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,51,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,52,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,53,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,54,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,55,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,56,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,57,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,58,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,59,0,0,0,null,0]
[null,null,null,null,null,null,null,null,null,null,60,0,0,0,null,0]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(P59:P82)+P56-C4","v":-1870.2734563257545}]

{"sheet":"Workings (A)","index":4,"n_rows":73,"n_cols":23,"rows_kept":73,"cut":false,"hidden":true,"merged":[[33,4,33,6]]}
["APR Calculation - Premium Credit Limit"]
[]
["This APR is calculated on the assumptions that:"]
["• As this calculation is being done at 'Quotation' PCL will be using the Predicted Credit Limit for these purposes "]
["• the agreement remains valid for the period agreed and that the parties fulfil their obligations under the terms and by the dates specified in the agreement;"]
["• the rate of the transaction fee will remain at the initial level and will be applicable for the duration of the agreement;"]
["• the Credit Limit is drawn down immediately and in full;"]
["• the credit is provided for a period of one year starting from the date of the initial drawdown, the capital is repaid in equal"]
["monthly payments commencing one month after the date of the initial drawdown, the final payment made by you clears the"]
["balance of capital and charges, and that charges are applied in accordance with that drawdown and repayments of capital and as"]
["provided for in the consumer credit agreement."]
[]
["Please input the variable data in the green highlighted fields and the APR can then be calculated using the assumptions below"]
[]
["Quotation"]
["Premium",null,{"$f":"Quotation!C16","v":1000000}]
["Loan Arrangement/Facility Fee",{"$f":"Quotation!C17","v":"Yes"},{"$f":"COUNTIF(B17,\"YES\")*IF(C16>E38,G38,IF(C16>E37,G37,IF(C16>E36,G36,\"error\")))","v":45}]
["No of Instalments",null,{"$f":"Quotation!C20","v":6}]
["Transaction Fee/Service Charge",{"$f":"Quotation!C18","v":2.4E-2},{"$f":"MAX((+C16*B19),C20)","v":24000}]
["Minimum Transaction Fee",{"$f":"IF(C20=0,\"No\",\"Yes\")","v":"Yes"},{"$f":"Quotation!C19","v":25}]
["Minimum Transaction Fee (%)",{"$f":"+C20/C16","v":2.5000000000000001E-5}]
["Total Amount Payable",null,{"$f":"+C16+C19+C17","v":1024045}]
["Predicted Credit Limit for APR Purposes",null,{"$f":"C16+C17+C19","v":1024045}]
["Initial Direct Debit of ",null,{"$f":"(+($C$16+$C$19)/$C$18)+C17","v":170711.66666666666}]
["Followed by X instalments of ",null,{"$f":"+(C16+C19)/C18","v":170666.66666666666}]
[]
["Representative APR"]
[null,{"$f":"K66","v":4.4999999999999998E-2},"APR Variable"]
[]
[]
[]
["APR Total Amount Payable Calculation"]
["Actual Credit Limit for Quotation Purposes",null,{"$f":"C23","v":1024045}]
["Transaction Fees",{"$f":"+B19","v":2.4E-2},{"$f":"MAX((+C33*B34),C38)","v":24577.08},null,"Fees Table"]
["Other Fees (eg Facility Fee/Loan Arrangement Fee)",{"$f":"+B17","v":"Yes"},{"$f":"COUNTIF(B35,\"YES\")*IF(C33>E38,G38,IF(C33>E37,G37,IF(C33>E36,G36,\"error\")))","v":45},null,"From","To","Fee"]
[null,null,{"$f":"+C33+C34+C35","v":1048667.08},null,1.0000000000000001E-5,{"$f":"+E37","v":999.99900000000002},10]
[null,null,null,null,999.99900000000002,{"$f":"+E38","v":4999.9989999999998},25]
["MSC (Min Service Charge)",{"$f":"+C38/C33","v":2.4412989663540178E-5},{"$f":"C20","v":25},null,4999.9989999999998,99999999,45]
[]
[]
["APR Calculation Input Values"]
["Total Amount Payable (in facility fees)",null,{"$f":"+C36","v":1048667.08},"Total Payable less Other Fees",null,{"$f":"+C42-C44","v":1048622.0800000001}]
["Credit Limit",null,{"$f":"C33","v":1024045},"Periodic Payment",null,{"$f":"((C43*(1+C45))/C46)","v":87385.17333333334},null,null,null,null,null,"Financed",null,null,null,null,null,null,null,null,{"$f":"C42","v":1048667.08}]
["Other Fees",null,{"$f":"+C35","v":45},null,null,null,null,"Credit Limit",null,null,{"$f":"-C43","v":-1024045},{"$f":"+K44+(K56*K60)","v":-1024045},null,"month","b/f","instal","net","interest - month","c/f","IRR","Total Payable","NPV","Rate"]
["Interest Rate (Flat)",null,{"$f":"MAX(+B34,B38)","v":2.4E-2},null,null,null,null,null,null,null,null,null,null,0,{"$f":"+K44+(K60*K56)","v":-1024045},0,{"$f":"+O45+P45","v":-1024045},{"$f":"+Q45*$K$62","v":-3762.8513493465534},{"$f":"+Q45+R45","v":-1027807.8513493466},{"$f":"L44+P45","v":-1024045},{"$f":"+U43-P45","v":1048667.08},{"$f":"+T45","v":-1024045}]
["Number of Periods",null,12,null,null,null,null,"Service Charge",null,{"$f":"+C45","v":2.4E-2},{"$f":"+K44*J46","v":-24577.08},null,null,1,{"$f":"+S45","v":-1027807.8513493466},{"$f":"IF(N46>K$58,0,K$60)+I50","v":87430.17333333334},-940377.67801601323,{"$f":"+Q46*$K$62","v":-3455.4159383795968},-943833.09395439283,{"$f":"+P46","v":87430.17333333334},{"$f":"+U45-P46","v":961236.90666666673},{"$f":"NPV(W46,T46)","v":87110.087487259065},{"$f":"(1+K$62)^N46-1","v":3.6744980438814245E-3}]
[null,null,null,null,null,null,null,null,"(net of any subsidy)",{"$f":"+L47/L44","v":2.4E-2},null,{"$f":"+K46","v":-24577.08},null,2,-943833.09395439283,{"$f":"IF(N47>K$58,0,K$60)","v":87385.17333333334},-856447.92062105949,-3147.0162090083968,-859594.93683006789,87385.17333333334,873851.7333333334,86746.502380995807,7.3624980236373094E-3]
[null,null,null,null,null,null,null,"Total Payable by Customer",null,null,{"$f":"+K44+K46","v":-1048622.0800000001},{"$f":"+L44+L47","v":-1048622.0800000001},null,3,-859594.93683006789,{"$f":"IF(N48>K$58,0,K$60)","v":87385.17333333334},-772209.76349673455,-2837.4832654348884,-775047.24676216941,87385.17333333334,786466.56,86428.919485411883,1.1064049552104605E-2]
["Proof",null,null,null,null,null,null,null,null,null,null,null,null,4,-775047.24676216941,87385.17333333334,-687662.07342883607,-2526.8129436657027,-690188.88637250173,87385.17333333334,699081.38666666672,86112.499275271155,1.4779202424422655E-2]
["Credit Limit",null,{"$f":"+C43","v":1024045},null,null,null,null,"Facility Fee",{"$f":"+C44","v":45},"YES",{"$f":"IF(J50=\"YES\",-I50,0)","v":-45},null,null,5,-690188.88637250173,87385.17333333334,-602803.71303916839,-2215.0010644068839,-605018.71410357533,87385.17333333334,611696.21333333338,85797.237493929293,1.8508006618702666E-2]
["Service Charge",null,{"$f":"+C50*C45","v":24577.08},null,null,null,null,"Balance Payable by Instalments",null,null,{"$f":"+K48+K50","v":-1048667.08},null,null,6,-605018.71410357533,87385.17333333334,-517633.54077024199,-1902.0434330076698,-519535.58420324966,87385.17333333334,524311.04000000004,85483.129900325672,2.225051229670072E-2]
["Other Fees",null,{"$f":"+C44","v":45},null,null,null,null,null,null,null,null,null,null,7,-519535.58420324966,87385.17333333334,-432150.41086991632,-1587.9358394040614,-433738.34670932038,87385.17333333334,436925.8666666667,85170.172268926472,2.6006769804491681E-2]
[null,null,{"$f":"+C50+C51+C52","v":1048667.08},null,null,null,null,null,null,null,null,null,null,8,-433738.34670932038,87385.17333333334,-346353.17337598704,-1272.6740580621884,-347625.84743404924,87385.17333333334,349540.69333333336,84858.36038966765,2.977682967314732E-2]
[null,null,null,null,null,null,null,"Instalments - Total payable by customer",null,null,{"$f":"+C46","v":12},null,null,9,-347625.84743404924,87385.17333333334,-260240.6741007159,-956.25384792146383,-261196.92794863737,87385.17333333334,262155.52000000002,84547.690067898468,3.3560742619415773E-2]
[null,null,null,null,null,null,null,null,null,null,null,null,null,10,-261196.92794863737,87385.17333333334,-173811.75461530403,-638.67095233753287,-174450.42556764156,87385.17333333334,174770.34666666668,84238.157124324964,3.7358559546403436E-2]
[null,null,null,null,null,null,null,"Instalments - held by broker",null,null,0,null,null,11,-174450.42556764156,87385.17333333334,-87065.252234308224,-319.9210990250084,-87385.173333333238,87385.17333333334,87385.17333333334,83929.757394953762,4.1170331544260197E-2]
[null,null,null,null,null,null,null," ",null,null,null,null,null,12,-87385.173333333238,87385.17333333334,0,0,0,87385.17333333334,0,83622.486731036086,4.4996109890866887E-2]
[null,null,null,null,null,null,null,"Instalments - payable to PCL",null,null,{"$f":"+K54-K56","v":12},{"$f":"+K58","v":12},null,null,null,{"$f":"SUM(P45:P57)","v":1048667.08},null,{"$f":"SUM(R45:R57)","v":-24622.079999999944},null,{"$f":"SUM(T45:T57)","v":24622.080000000075},null,{"$f":"SUM(V45:V57)","v":2.6193447411060333E-10}]
[]
[null,null,null,null,null,null,null,"Amount of instalment",null,null,{"$f":"F43","v":87385.17333333334},{"$f":"+K60","v":87385.17333333334}]
[]
[null,null,null,null,null,null,null,"Interest Rate","Per Month",null,{"$f":"IRR(T45:T57,1%)","v":3.6744980438814245E-3}]
[null,null,null,null,null,null,null,"Interest Rate","Annual",null,{"$f":"+K62*12","v":4.4093976526577094E-2}]
[]
[null,null,null,null,null,null,null,"APR","Precise",null,{"$f":"((1+K62)^12)-1","v":4.4996109890866887E-2}]
[null,null,null,null,null,null,null,"APR","Official",null,{"$f":"ROUND(+K65*100,1)/100","v":4.4999999999999998E-2}]
[]
[null,null,null,null,null,null,null,"Start",null,null,"2014-01-01"]
[null,null,null,null,null,null,null,null,null,null," "]
[null,null,null,null,null,null,null,"End",null,null,{"$f":"+K68+K54*(365/12)","v":"2015-01-01"}]
[]
[null,null,null,null,null,null,"N.B.","The computation does not require dates"]
[null,null,null,null,null,null,null,"Assumed that all Instalments are paid monthly in arrears commencing on day 1 of month 2"]

{"sheet":"Workings (B)","index":5,"n_rows":73,"n_cols":23,"rows_kept":73,"cut":false,"hidden":true,"merged":[[33,4,33,6]]}
["APR Calculation - Premium Credit Limit"]
[]
["This APR is calculated on the assumptions that:"]
["• As this calculation is being done at 'Quotation' PCL will be using the Predicted Credit Limit for these purposes "]
["• the agreement remains valid for the period agreed and that the parties fulfil their obligations under the terms and by the dates specified in the agreement;"]
["• the rate of the transaction fee will remain at the initial level and will be applicable for the duration of the agreement;"]
["• the Credit Limit is drawn down immediately and in full;"]
["• the credit is provided for a period of one year starting from the date of the initial drawdown, the capital is repaid in equal"]
["monthly payments commencing one month after the date of the initial drawdown, the final payment made by you clears the"]
["balance of capital and charges, and that charges are applied in accordance with that drawdown and repayments of capital and as"]
["provided for in the consumer credit agreement."]
[]
["Please input the variable data in the green highlighted fields and the APR can then be calculated using the assumptions below"]
[]
["Quotation"]
["Premium",null,{"$f":"Quotation!C16","v":1000000}]
["Loan Arrangement/Facility Fee",{"$f":"Quotation!D17","v":"Yes"},{"$f":"COUNTIF(B17,\"YES\")*IF(C16>E38,G38,IF(C16>E37,G37,IF(C16>E36,G36,\"error\")))","v":45}]
["No of Instalments",null,{"$f":"Quotation!D20","v":8}]
["Transaction Fee/Service Charge",{"$f":"Quotation!D18","v":3.2000000000000001E-2},{"$f":"MAX((+C16*B19),C20)","v":32000}]
["Minimum Transaction Fee",{"$f":"IF(C20=0,\"No\",\"Yes\")","v":"Yes"},{"$f":"Quotation!D19","v":25}]
["Minimum Transaction Fee (%)",{"$f":"+C20/C16","v":2.5000000000000001E-5}]
["Total Amount Payable",null,{"$f":"+C16+C19+C17","v":1032045}]
["Predicted Credit Limit for APR Purposes",null,{"$f":"C16+C17+C19","v":1032045}]
["Initial Direct Debit of ",null,{"$f":"(+($C$16+$C$19)/$C$18)+C17","v":129045}]
["Followed by X instalments of ",null,{"$f":"+(C16+C19)/C18","v":129000}]
[]
["Representative APR"]
[null,{"$f":"K66","v":0.06},"APR Variable"]
[]
[]
[]
["APR Total Amount Payable Calculation"]
["Actual Credit Limit for Quotation Purposes",null,{"$f":"C23","v":1032045}]
["Transaction Fees",{"$f":"+B19","v":3.2000000000000001E-2},{"$f":"MAX((+C33*B34),C38)","v":33025.440000000002},null,"Fees Table"]
["Other Fees (eg Facility Fee/Loan Arrangement Fee)",{"$f":"+B17","v":"Yes"},{"$f":"COUNTIF(B35,\"YES\")*IF(C33>E38,G38,IF(C33>E37,G37,IF(C33>E36,G36,\"error\")))","v":45},null,"From","To","Fee"]
[null,null,{"$f":"+C33+C34+C35","v":1065115.44},null,1.0000000000000001E-5,{"$f":"+E37","v":999.99900000000002},{"$f":"'Workings (A)'!G36","v":10}]
[null,null,null,null,999.99900000000002,{"$f":"+E38","v":4999.9989999999998},{"$f":"'Workings (A)'!G37","v":25}]
["MSC (Min Service Charge)",{"$f":"+C38/C33","v":2.4223749933384687E-5},{"$f":"C20","v":25},null,4999.9989999999998,99999999,{"$f":"'Workings (A)'!G38","v":45}]
[]
[]
["APR Calculation Input Values"]
["Total Amount Payable (in facility fees)",null,{"$f":"+C36","v":1065115.44},"Total Payable less Other Fees",null,{"$f":"+C42-C44","v":1065070.44}]
["Credit Limit",null,{"$f":"C33","v":1032045},"Periodic Payment",null,{"$f":"((C43*(1+C45))/C46)","v":88755.87},null,null,null,null,null,"Financed",null,null,null,null,null,null,null,null,{"$f":"C42","v":1065115.44}]
["Other Fees",null,{"$f":"+C35","v":45},null,null,null,null,"Credit Limit",null,null,{"$f":"-C43","v":-1032045},{"$f":"+K44+(K56*K60)","v":-1032045},null,"month","b/f","instal","net","interest - month","c/f","IRR","Total Payable","NPV","Rate"]
["Interest Rate (Flat)",null,{"$f":"MAX(+B34,B38)","v":3.2000000000000001E-2},null,null,null,null,null,null,null,null,null,null,0,{"$f":"+K44+(K60*K56)","v":-1032045},0,{"$f":"+O45+P45","v":-1032045},{"$f":"+Q45*$K$62","v":-5042.8844275068113},{"$f":"+Q45+R45","v":-1037087.8844275068},{"$f":"L44+P45","v":-1032045},{"$f":"+U43-P45","v":1065115.44},{"$f":"+T45","v":-1032045}]
["Number of Periods",null,12,null,null,null,null,"Service Charge",null,{"$f":"+C45","v":3.2000000000000001E-2},{"$f":"+K44*J46","v":-33025.440000000002},null,null,1,{"$f":"+S45","v":-1037087.8844275068},{"$f":"IF(N46>K$58,0,K$60)+I50","v":88800.87},-948287.01442750683,{"$f":"+Q46*$K$62","v":-4633.6175436762942},-952920.63197118312,{"$f":"+P46","v":88800.87},{"$f":"+U45-P46","v":976314.57},{"$f":"NPV(W46,T46)","v":88369.071951641483},{"$f":"(1+K$62)^N46-1","v":4.8863028525953922E-3}]
[null,null,null,null,null,null,null,null,"(net of any subsidy)",{"$f":"+L47/L44","v":3.2000000000000001E-2},null,{"$f":"+K46","v":-33025.440000000002},null,2,-952920.63197118312,{"$f":"IF(N47>K$58,0,K$60)","v":88755.87},-864164.76197118312,-4222.5707415322104,-868387.33271271538,88755.87,887558.7,87894.810104733158,9.7964816607580829E-3]
[null,null,null,null,null,null,null,"Total Payable by Customer",null,null,{"$f":"+K44+K46","v":-1065070.44},{"$f":"+L44+L47","v":-1065070.44},null,3,-868387.33271271538,{"$f":"IF(N48>K$58,0,K$60)","v":88755.87},-779631.46271271538,-3809.5154402262592,-783440.97815294168,88755.87,798802.83,87467.417811571329,1.4730653089637835E-2]
["Proof",null,null,null,null,null,null,null,null,null,null,null,null,4,-783440.97815294168,88755.87,-694685.10815294168,-3394.4418256232575,-698079.54997856496,88755.87,710046.96,87042.103731810683,1.9688934374445743E-2]
["Credit Limit",null,{"$f":"+C43","v":1032045},null,null,null,null,"Facility Fee",{"$f":"+C44","v":45},"YES",{"$f":"IF(J50=\"YES\",-I50,0)","v":-45},null,null,5,-698079.54997856496,88755.87,-609323.67997856496,-2977.340035633184,-612301.02001419815,88755.87,621291.09,86618.857760049199,2.4671443323239561E-2]
["Service Charge",null,{"$f":"+C50*C45","v":33025.440000000002},null,null,null,null,"Balance Payable by Instalments",null,null,{"$f":"+K48+K50","v":-1065115.44},null,null,6,-612301.02001419815,88755.87,-523545.15001419815,-2558.2001599768591,-526103.35017417499,88755.87,532535.22,86197.669840022805,2.9678298319723018E-2]
["Other Fees",null,{"$f":"+C44","v":45},null,null,null,null,null,null,null,null,null,null,7,-526103.35017417499,88755.87,-437347.480174175,-2137.012239950478,-439484.49241412547,88755.87,443779.35,85778.529964366491,3.4709618326058234E-2]
[null,null,{"$f":"+C50+C51+C52","v":1065115.44},null,null,null,null,null,null,null,null,null,null,8,-439484.49241412547,88755.87,-350728.62241412548,-1713.7662681889935,-352442.38868231449,88755.87,355023.48,85361.428174376415,3.9765522885692794E-2]
[null,null,null,null,null,null,null,"Instalments - Total payable by customer",null,null,{"$f":"+C46","v":12},null,null,9,-352442.38868231449,88755.87,-263686.51868231449,-1288.4521884283415,-264974.97087074281,88755.87,266267.61,84946.354559773405,4.4846132126199478E-2]
[null,null,null,null,null,null,null,null,null,null,null,null,null,10,-264974.97087074281,88755.87,-176219.10087074281,-861.05989526650581,-177080.16076600933,88755.87,177511.74,84533.29925846745,4.9951566762131083E-2]
[null,null,null,null,null,null,null,"Instalments - held by broker",null,null,0,null,null,11,-177080.16076600933,88755.87,-88324.290766009333,-431.57923392341627,-88755.869999932751,88755.87,88755.87,84122.252456323375,5.5081948097887912E-2]
[null,null,null,null,null,null,null," ",null,null,null,null,null,12,-88755.869999932751,88755.87,6.7244400270283222E-8,3.285765048617513E-10,6.7572976775144968E-8,88755.87,0,83713.204386927639,6.0237398030600353E-2]
[null,null,null,null,null,null,null,"Instalments - payable to PCL",null,null,{"$f":"+K54-K56","v":12},{"$f":"+K58","v":12},null,null,null,{"$f":"SUM(P45:P57)","v":1065115.44},null,{"$f":"SUM(R45:R57)","v":-33070.439999932285},null,{"$f":"SUM(T45:T57)","v":33070.439999999944},null,{"$f":"SUM(V45:V57)","v":6.3388142734766006E-8}]
[]
[null,null,null,null,null,null,null,"Amount of instalment",null,null,{"$f":"F43","v":88755.87},{"$f":"+K60","v":88755.87}]
[]
[null,null,null,null,null,null,null,"Interest Rate","Per Month",null,{"$f":"IRR(T45:T57,1%)","v":4.8863028525953922E-3}]
[null,null,null,null,null,null,null,"Interest Rate","Annual",null,{"$f":"+K62*12","v":5.8635634231144707E-2}]
[]
[null,null,null,null,null,null,null,"APR","Precise",null,{"$f":"((1+K62)^12)-1","v":6.0237398030600353E-2}]
[null,null,null,null,null,null,null,"APR","Official",null,{"$f":"ROUND(+K65*100,1)/100","v":0.06}]
[]
[null,null,null,null,null,null,null,"Start",null,null,"2014-01-01"]
[null,null,null,null,null,null,null,null,null,null," "]
[null,null,null,null,null,null,null,"End",null,null,{"$f":"+K68+K54*(365/12)","v":"2015-01-01"}]
[]
[null,null,null,null,null,null,"N.B.","The computation does not require dates"]
[null,null,null,null,null,null,null,"Assumed that all Instalments are paid monthly in arrears commencing on day 1 of month 2"]

{"sheet":"Workings (C)","index":6,"n_rows":73,"n_cols":23,"rows_kept":73,"cut":false,"hidden":true,"merged":[[33,4,33,6]]}
["APR Calculation - Premium Credit Limit"]
[]
["This APR is calculated on the assumptions that:"]
["• As this calculation is being done at 'Quotation' PCL will be using the Predicted Credit Limit for these purposes "]
["• the agreement remains valid for the period agreed and that the parties fulfil their obligations under the terms and by the dates specified in the agreement;"]
["• the rate of the transaction fee will remain at the initial level and will be applicable for the duration of the agreement;"]
["• the Credit Limit is drawn down immediately and in full;"]
["• the credit is provided for a period of one year starting from the date of the initial drawdown, the capital is repaid in equal"]
["monthly payments commencing one month after the date of the initial drawdown, the final payment made by you clears the"]
["balance of capital and charges, and that charges are applied in accordance with that drawdown and repayments of capital and as"]
["provided for in the consumer credit agreement."]
[]
["Please input the variable data in the green highlighted fields and the APR can then be calculated using the assumptions below"]
[]
["Quotation"]
["Premium",null,{"$f":"Quotation!C16","v":1000000}]
["Loan Arrangement/Facility Fee",{"$f":"Quotation!E17","v":"Yes"},{"$f":"COUNTIF(B17,\"YES\")*IF(C16>E38,G38,IF(C16>E37,G37,IF(C16>E36,G36,\"error\")))","v":45}]
["No of Instalments",null,{"$f":"Quotation!E20","v":10}]
["Transaction Fee/Service Charge",{"$f":"Quotation!E18","v":3.95E-2},{"$f":"MAX((+C16*B19),C20)","v":39500}]
["Minimum Transaction Fee",{"$f":"IF(C20=0,\"No\",\"Yes\")","v":"Yes"},{"$f":"Quotation!E19","v":25}]
["Minimum Transaction Fee (%)",{"$f":"+C20/C16","v":2.5000000000000001E-5}]
["Total Amount Payable",null,{"$f":"+C16+C19+C17","v":1039545}]
["Predicted Credit Limit for APR Purposes",null,{"$f":"C16+C17+C19","v":1039545}]
["Initial Direct Debit of ",null,{"$f":"(+($C$16+$C$19)/$C$18)+C17","v":103995}]
["Followed by X instalments of ",null,{"$f":"+(C16+C19)/C18","v":103950}]
[]
["Representative APR"]
[null,{"$f":"K66","v":7.4999999999999997E-2},"APR Variable"]
[]
[]
[]
["APR Total Amount Payable Calculation"]
["Actual Credit Limit for Quotation Purposes",null,{"$f":"C23","v":1039545}]
["Transaction Fees",{"$f":"+B19","v":3.95E-2},{"$f":"MAX((+C33*B34),C38)","v":41062.027500000004},null,"Fees Table"]
["Other Fees (eg Facility Fee/Loan Arrangement Fee)",{"$f":"+B17","v":"Yes"},{"$f":"COUNTIF(B35,\"YES\")*IF(C33>E38,G38,IF(C33>E37,G37,IF(C33>E36,G36,\"error\")))","v":45},null,"From","To","Fee"]
[null,null,{"$f":"+C33+C34+C35","v":1080652.0275000001},null,1.0000000000000001E-5,{"$f":"+E37","v":999.99900000000002},{"$f":"'Workings (A)'!G36","v":10}]
[null,null,null,null,999.99900000000002,{"$f":"+E38","v":4999.9989999999998},{"$f":"'Workings (A)'!G37","v":25}]
["MSC (Min Service Charge)",{"$f":"+C38/C33","v":2.4048982968510262E-5},{"$f":"C20","v":25},null,4999.9989999999998,99999999,{"$f":"'Workings (A)'!G38","v":45}]
[]
[]
["APR Calculation Input Values"]
["Total Amount Payable (in facility fees)",null,{"$f":"+C36","v":1080652.0275000001},"Total Payable less Other Fees",null,{"$f":"+C42-C44","v":1080607.0275000001}]
["Credit Limit",null,{"$f":"C33","v":1039545},"Periodic Payment",null,{"$f":"((C43*(1+C45))/C46)","v":90050.585625000007},null,null,null,null,null,"Financed",null,null,null,null,null,null,null,null,{"$f":"C42","v":1080652.0275000001}]
["Other Fees",null,{"$f":"+C35","v":45},null,null,null,null,"Credit Limit",null,null,{"$f":"-C43","v":-1039545},{"$f":"+K44+(K56*K60)","v":-1039545},null,"month","b/f","instal","net","interest - month","c/f","IRR","Total Payable","NPV","Rate"]
["Interest Rate (Flat)",null,{"$f":"MAX(+B34,B38)","v":3.95E-2},null,null,null,null,null,null,null,null,null,null,0,{"$f":"+K44+(K60*K56)","v":-1039545},0,{"$f":"+O45+P45","v":-1039545},{"$f":"+Q45*$K$62","v":-6255.5880445846615},{"$f":"+Q45+R45","v":-1045800.5880445846},{"$f":"L44+P45","v":-1039545},{"$f":"+U43-P45","v":1080652.0275000001},{"$f":"+T45","v":-1039545}]
["Number of Periods",null,12,null,null,null,null,"Service Charge",null,{"$f":"+C45","v":3.95E-2},{"$f":"+K44*J46","v":-41062.027500000004},null,null,1,{"$f":"+S45","v":-1045800.5880445846},{"$f":"IF(N46>K$58,0,K$60)+I50","v":90095.585625000007},-955705.00241958466,{"$f":"+Q46*$K$62","v":-5751.0706965890931},-961456.07311617373,{"$f":"+P46","v":90095.585625000007},{"$f":"+U45-P46","v":990556.44187500002},{"$f":"NPV(W46,T46)","v":89556.667522688163},{"$f":"(1+K$62)^N46-1","v":6.017621213689317E-3}]
[null,null,null,null,null,null,null,null,"(net of any subsidy)",{"$f":"+L47/L44","v":3.95E-2},null,{"$f":"+K46","v":-41062.027500000004},null,2,-961456.07311617373,{"$f":"IF(N47>K$58,0,K$60)","v":90050.585625000007},-871405.48749117367,-5243.7881472521676,-876649.27563842584,90050.585625000007,900505.85624999995,88976.509763189577,1.2071454192450037E-2]
[null,null,null,null,null,null,null,"Total Payable by Customer",null,null,{"$f":"+K44+K46","v":-1080607.0275000001},{"$f":"+L44+L47","v":-1080607.0275000001},null,3,-876649.27563842584,{"$f":"IF(N48>K$58,0,K$60)","v":90050.585625000007},-786598.69001342589,-4733.4529636850184,-791332.14297711093,90050.585625000007,810455.27062499989,88444.285554208982,1.8161716844967923E-2]
["Proof",null,null,null,null,null,null,null,null,null,null,null,null,4,-791332.14297711093,90050.585625000007,-701281.55735211098,-4220.0467762911449,-705501.60412840208,90050.585625000007,720404.68499999982,87915.244911423302,2.4288628391220479E-2]
["Credit Limit",null,{"$f":"+C43","v":1039545},null,null,null,null,"Facility Fee",{"$f":"+C44","v":45},"YES",{"$f":"IF(J50=\"YES\",-I50,0)","v":-45},null,null,5,-705501.60412840208,90050.585625000007,-615451.01850340213,-3703.5511049327688,-619154.56960833492,90050.585625000007,630354.09937499976,87389.368791930072,3.0452409370368194E-2]
["Service Charge",null,{"$f":"+C50*C45","v":41062.027500000004},null,null,null,null,"Balance Payable by Instalments",null,null,{"$f":"+K48+K50","v":-1080652.0275000001},null,null,6,-619154.56960833492,90050.585625000007,-529103.98398333485,-3183.9473582656483,-532287.93134160049,90050.585625000007,540303.51374999969,86866.638266734299,3.6653281648692637E-2]
["Other Fees",null,{"$f":"+C44","v":45},null,null,null,null,null,null,null,null,null,null,7,-532287.93134160049,90050.585625000007,-442237.34571660048,-2661.2168330698714,-444898.56254967034,90050.585625000007,450252.92812499969,86347.034520067187,4.2891468427582291E-2]
[null,null,{"$f":"+C50+C51+C52","v":1080652.0275000001},null,null,null,null,null,null,null,null,null,null,8,-444898.56254967034,90050.585625000007,-354847.97692467034,-2135.3407135766333,-356983.31763824698,90050.585625000007,360202.34249999968,85830.53884870882,4.9167194251567681E-2]
[null,null,null,null,null,null,null,"Instalments - Total payable by customer",null,null,{"$f":"+C46","v":12},null,null,9,-356983.31763824698,90050.585625000007,-266932.73201324698,-1606.3000707909605,-268539.03208403796,90050.585625000007,270151.75687499967,85317.132661314929,5.5480685016402909E-2]
[null,null,null,null,null,null,null,null,null,null,null,null,null,10,-268539.03208403796,90050.585625000007,-178488.44645903795,-1074.0758618103566,-179562.52232084831,90050.585625000007,180101.17124999966,84806.797477747808,6.1832167977196706E-2]
[null,null,null,null,null,null,null,"Instalments - held by broker",null,null,0,null,null,11,-179562.52232084831,90050.585625000007,-89511.936695848301,-538.64892913935194,-90050.585624987652,90050.585625000007,90050.585624999658,84299.514928410863,6.822187175659411E-2]
[null,null,null,null,null,null,null," ",null,null,null,null,null,12,-90050.585624987652,90050.585625000007,1.2354576028883457E-8,7.4345158797546612E-11,1.2428921187681004E-8,90050.585625000007,-3.4924596548080444E-10,83795.266753587726,7.465002635300344E-2]
[null,null,null,null,null,null,null,"Instalments - payable to PCL",null,null,{"$f":"+K54-K56","v":12},{"$f":"+K58","v":12},null,null,null,{"$f":"SUM(P45:P57)","v":1080652.0275000003},null,{"$f":"SUM(R45:R57)","v":-41107.027499987598},null,{"$f":"SUM(T45:T57)","v":41107.027500000375},null,{"$f":"SUM(V45:V57)","v":1.1743395589292049E-8}]
[]
[null,null,null,null,null,null,null,"Amount of instalment",null,null,{"$f":"F43","v":90050.585625000007},{"$f":"+K60","v":90050.585625000007}]
[]
[null,null,null,null,null,null,null,"Interest Rate","Per Month",null,{"$f":"IRR(T45:T57,1%)","v":6.017621213689317E-3}]
[null,null,null,null,null,null,null,"Interest Rate","Annual",null,{"$f":"+K62*12","v":7.2211454564271804E-2}]
[]
[null,null,null,null,null,null,null,"APR","Precise",null,{"$f":"((1+K62)^12)-1","v":7.465002635300344E-2}]
[null,null,null,null,null,null,null,"APR","Official",null,{"$f":"ROUND(+K65*100,1)/100","v":7.4999999999999997E-2}]
[]
[null,null,null,null,null,null,null,"Start",null,null,"2014-01-01"]
[null,null,null,null,null,null,null,null,null,null," "]
[null,null,null,null,null,null,null,"End",null,null,{"$f":"+K68+K54*(365/12)","v":"2015-01-01"}]
[]
[null,null,null,null,null,null,"N.B.","The computation does not require dates"]
[null,null,null,null,null,null,null,"Assumed that all Instalments are paid monthly in arrears commencing on day 1 of month 2"]