{"text_format":"1","workbook":"xlsx","n_sheets":10,"sheets":["1 - Cover","2 - WAL Summary","3 - Inputs","4- Senior Debt","5 - Sub Debt & Equity","6- Single Asset","7- GDP Deflator","8 - Real Gilt","Charts>>","Chart Format Instructions"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":1,"defined_names":16,"comments":1,"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":"1 - Cover","index":0,"n_rows":67,"n_cols":7,"rows_kept":67,"cut":false,"hidden":false,"merged":[]}
[]
[null,"File Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"[\",CELL(\"filename\",G2))+1,FIND(\"]\",CELL(\"filename\",G2))-FIND(\"[\",CELL(\"filename\",G2))-1),\"Please save the file to show here\")","v":"weighted-average-life-example-financial-model.xlsm"}]
[null,"Sheet Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"]\",CELL(\"filename\",G2))+1,255),\"Please save the file to show here\")","v":"1 - Cover"}]
[null,"Description and Purpose of Sheet",null,null,null,null,"Weighted Average Life Calculation Example"]
[null,"Version number",null,null,null,null,1]
[null,"Date",null,null,null,null,{"$f":"TODAY()","v":"2026-05-20"}]
[]
[]
[null,"WAL Example Cover Sheet"]
[]
[null,null,"Purpose"]
[]
[null,null,"The objective is to determine the Weighted Average Life (WAL) of a project‑finance capital structure. WAL measures the average time it takes for lenders to receive their principal back, weighted by the timing of each repayment."]
[null,null,"This is a core metric used by lenders, sponsors, and financial advisers to benchmark debt pricing and compare maturities across structures."]
[null,null,"The calculations in this workbook are a simulation to produce a total cashflow to be discounted (and whose WAL needs to be known)  "]
[]
[null,null,"Scenario"]
[]
[null,null,"This workbook illustrates a stylised project‑finance structure comprising senior debt, subordinated debt and equity. "]
[null,null,"Subordinated debt increases through capitalised interest during early years when no operating cash is available, reflecting typical construction‑period funding in PFI/PPP‑style projects. "]
[null,null,"Once operational, senior debt amortises on a fixed annuity basis, while subordinated debt receives cash interest and limited principal repayments based on mechanics that are standard for project finance projects."]
[]
[null,null,"What the WAL represents"]
[]
[null,null,"WAL is not the tenor of the loan – it reflects effective maturity, which depends on how quickly principal is repaid."]
[null,null,"A back‑ended principal repayment structure → long WAL"]
[null,null,"A front‑loaded or sculpted principal repayment structure → shorter WAL"]
[null,null,"WAL aligns debt pricing to actual risk exposure, since lenders are exposed for longer if principal is repaid later in the life of the asset."]
[]
[null,null,"How to use the WAL"]
[]
[null,null,"With the Weighted Average Life (WAL) now calculated, the next step is to use it as the anchor point for debt pricing and benchmarking. WAL tells you the effective maturity of the senior debt—i.e., the average time over which lenders are exposed to principal risk. "]
[null,null,"Once the WAL is known, it can be matched to the nearest point on the UK gilt curve to identify the appropriate reference rate for analysis or pricing. In practice, you would take the WAL value—say 11.8 years—and look up the corresponding gilt yield at roughly the same maturity. This ensures the cost of debt reflects the true duration of lender exposure rather than the headline loan tenor, creating a more accurate basis for evaluating financing options, setting margins, and comparing structure-to-structure."]
[null,null,"This ensures the cost of debt reflects the true duration of lender exposure rather than the headline loan tenor, creating a more accurate basis for evaluating financing options, setting margins, and comparing structure-to-structure."]
[]
[null,null,"Outputs"]
[]
[null,null,"These are shown in the WAL Sumamry worksheet. The results based on four different calculation methodologies are shown "]
[null,null,"(1) a weighted average based on the proportion of finance from each type of finance with principal repayments only, "]
[null,null,"(2) similar to (1) but using both interest and principal repayments, "]
[null,null,"(3) a weighted average based on combining the cashflows of all the finance sources into one overall 'notional' facility. It is then assumed that these cashflow are used against a GILT with the same principal amount (with the interest rate fixed based on using up all of the cashflows). The WAL is then calculated based on the principal repayments of the notional GILT facility, and "]
[null,null,"(4) similar to (3) but based on principal and interest payments."]
[null,null,"We would recommend using result given by (3) unless there is a particular reason not to. Considering principal only is the more standard way of calculating a WAL. And combining the finance cashflows into one 'notional GILT facility' best replicates the approach HMG is most likely to take in financing the project entirely from GILTS (rather than splitting into different types of finance with different rates of return). "]
[]
[]
[null,null,"Notes on use"]
[]
[null,null,"1) The WAL in this model is based on the combined cashflows of senior debt, subordinated debt and equity‑like capital. Each instrument has a different repayment profile and effective maturity, so the model calculates a blended “Combo” WAL that weights all financing sources according to the timing of their principal‑like cashflows. This provides the most representative WAL for pricing, benchmarking or aligning to gilt curve maturities."]
[null,null,"2) This calculation is based on the Weighted Average Life. There is alternative of using the 'Weighted Average Duration\", but using WAL is simpler and for consistency we recommend using it unless there is a particular reason it is not appropriate."]
[null,null,"3) It is important to understand whether you need the real or nominal GILT rate. Below sets out where you can look up either."]
[null,null,"4) This spreadsheet provides an example case for an WAL calcuation to show projects a consistent basis for calculation. However, on an specific project, we would recommend that the WAL is calculated within the project financing model to make sure the correct inputs are used, but based on the methodology set out in this spreadsheet."]
[null,null,"5) In practice, all you will need to do is to perform the calculation shown on the Single-Asset sheet using the outputs from the model of the real project you are working on."]
[null,null,"NB: Taxation (Corporation Tax) is not included in this illustrative model. In practice, UK project‑finance SPVs calculate taxable profit after interest deductions and capital allowances, typically generating tax losses during construction and early operation. These deferred tax effects can materially affect CFADS and gearing over time, but they are excluded here for simplicity."]
[]
[]
[null,null,"Notes on use"]
[]
[null,null,"- Go to: Bank of England → Statistics → Yield curves"]
[null,null,"https://www.bankofengland.co.uk/statistics/yield-curves"]
[null,null,"(BoE publishes daily estimated UK gilt curves and downloadable data.)"]
[null,null,"- Download the latest GILT yield curve files. Within these use the relevant data set - which is likely to be either the 'spot curve' worsheet within the  \"GLC Nominal daily data current month\". If you use the real rates, you need to be aware that the market implied inflation will likely be different from the inflation assumptions that are being used elsewhere in the analysis, so this may create an inconsistency in inflation assumptions. For consistency, it is therefore recommended to use the nominal rates and deflate these at the inflation assumption being used elsewhere in the appraisal. This is likely the latest GDP deflator series in the TAG data book. The rates to use should be the “UK nominal spot curve”. These are the zero‑coupon/spot rates by maturity used for benchmarking and interpolation."]
[null,null,"- Match your WAL to maturity and read the rate of the nearest benchmark of 5, 10 or 30 years:"]
[null,null,"We do the above to match with the periods government usually issues debt, and are hence liquid periods. "]
[]
[]
[]
[null,"END"]

{"sheet":"2 - WAL Summary","index":1,"n_rows":22,"n_cols":6,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[]
[null,"File Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"[\",CELL(\"filename\",D2))+1,FIND(\"]\",CELL(\"filename\",D2))-FIND(\"[\",CELL(\"filename\",D2))-1),\"Please save the file to show here\")","v":"weighted-average-life-example-financial-model.xlsm"}]
[null,"Sheet Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"]\",CELL(\"filename\",D2))+1,255),\"Please save the file to show here\")","v":"2 - WAL Summary"}]
[null,"Description and Purpose of Sheet",null,"Weighted Average Life Calculation Example"]
[null,"Version number",null,1]
[null,"Date",null,{"$f":"TODAY()","v":"2026-05-20"}]
[]
[]
[null,"WAL Summary Results"]
[]
[null,"Facility",null,null,"Total Principal","WAL-Principal (years)"]
[null,"Senior Debt",null,null,{"$f":"'3 - Inputs'!D20","v":90000000},{"$f":"'4- Senior Debt'!D20","v":12.097034876276529}]
[null,"Subordinated Debt",null,null,{"$f":"'3 - Inputs'!D28","v":7999999.9999999991},{"$f":"'5 - Sub Debt & Equity'!C20","v":6.1912969695988744}]
[null,"Equity",null,null,{"$f":"'3 - Inputs'!D34","v":1999999.9999999991},{"$f":"'5 - Sub Debt & Equity'!F20","v":23.34372349760546}]
[null,"Weighted average based on proportion of finance",null,null,{"$f":"SUM(E12:E14)","v":100000000},{"$f":"SUMPRODUCT($E12:$E14,F12:F14)/$E15","v":11.849509616168895}]
[]
[null,"Combo ",null,null,{"$f":"'3 - Inputs'!D16","v":100000000},{"$f":"'6- Single Asset'!D23","v":16.407339080397929}]
[]
[null,"Conclusion"]
[null,"We would recommend using the WAL highlighted in green, which is based on (a) principal repayments only, and (b) considering a total combined level of finance for the project. Please see the cover sheet for more information."]
[]
["END"]

{"sheet":"3 - Inputs","index":2,"n_rows":48,"n_cols":9,"rows_kept":48,"cut":false,"hidden":false,"merged":[[45,2,45,15]]}
[]
[null,"File Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"[\",CELL(\"filename\",D2))+1,FIND(\"]\",CELL(\"filename\",D2))-FIND(\"[\",CELL(\"filename\",D2))-1),\"Please save the file to show here\")","v":"weighted-average-life-example-financial-model.xlsm"}]
[null,"Sheet Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"]\",CELL(\"filename\",D2))+1,255),\"Please save the file to show here\")","v":"3 - Inputs"}]
[null,"Description and Purpose of Sheet",null,"Weighted Average Life Calculation Example"]
[null,"Version number",null,1]
[null,"Date",null,{"$f":"TODAY()","v":"2026-05-20"}]
[]
[]
[null,"Inputs"]
[]
[null,null,"Parameter","Value",null,"Parameter","Value",null,"Notes"]
[]
[null,null,"Start Date","2026-04-01"]
[]
[null,null,"Assets and Liabilities Assumptions / Inputs",null,null,"Cashflow Assumptons / Inputs"]
[null,null,"Total Asets",100000000]
[]
[null,null,"Senior Gearing (Senior Debt / Total Assets)",0.9]
[]
[null,null,"Senior Debt Amount",{"$f":"D16*D18","v":90000000}]
[]
[null,null,"Senior Debt Interest Rate",0.05]
[]
[null,null,"Senior Debt Term (years)",20,null,"Average Annual Debt Service",{"$f":"-PMT($D$22,$D$24,$D$20)","v":7221832.8471622188}]
[]
[null,null,"Junior Gearing (Sub-Debt / Equity)",0.8,null,"Assumed ADSCR",1.2]
[]
[null,null,"Subordinated Debt Amount",{"$f":"D16*(1-D18)*D26","v":7999999.9999999991},null,"CFADS",{"$f":"G26*G24","v":8666199.4165946618},null,"[DBFM-PPP Models are back-solved to calculate this CFADS, to which operational expenses are added to backsolve annual revenue / availability payment]"]
[]
[null,null,"Sub Debt Rate ",0.12,null,"Cashflow to pay Jnr Debt and Equity BEFORE Senior Debt Repaid",{"$f":"G28-G24","v":1444366.569432443}]
[]
[null,null,"Sub Debt Term (years)",10,null,"Cashflow to pay Jnr Debt and Equity AFTER  Senior Debt Repaid","CFADS",null,"[if the senior debt has a shorter final maturity than the stream of availability payments all of CFADS is available to pay junior capital]"]
[null,null,null,null,null,null,null,null,"[in practice the junior debt service and dividends are owed to the same entity but whereas dividends and capital reductions are subject to limits by company law, junior debt service can be paid without any restriction (except there being sufficient cash) so is usually assumed to be paid first"]
[null,null,"Ordinary Share Capital",{"$f":"D16*(1-D18)*(1-D26)","v":1999999.9999999991}]
[]
[null,null,"Concession period (years)",{"$f":"MAX('4- Senior Debt'!$C$26:$C$50)","v":25},null,null,null,null,"[Concession period refers to the life of the project span for which the private sector is in control. This should also consider the length of the pay back time of shareholder capital. In this example, the senior debt term is 20 years, however a further five years is required as availability payments continue and fund the return of shareholder capital through to year 25. ]"]
[]
[null,null,"Check"]
[null,null,"Total Assets",{"$f":"D20+D28+D34","v":100000000}]
[null,null,"Senior Debt",{"$f":"D20","v":90000000},{"$f":"D40/$D$39","v":0.9}]
[null,null,"Sub Debt",{"$f":"$D$28","v":7999999.9999999991},{"$f":"D41/$D$39","v":7.9999999999999988E-2}]
[null,null,"Share Capital / Equity",{"$f":"D34","v":1999999.9999999991},1.999999999999999E-2]
[null,null,"Total Liabilities",{"$f":"SUM(D40:D42)","v":100000000},{"$f":"D43/$D$39","v":1}]
[]
[]
[]
[]
[null,"END"]

{"sheet":"4- Senior Debt","index":3,"n_rows":94,"n_cols":9,"rows_kept":94,"cut":false,"hidden":false,"merged":[[91,2,91,15]]}
[]
[null,"File Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"[\",CELL(\"filename\",D2))+1,FIND(\"]\",CELL(\"filename\",D2))-FIND(\"[\",CELL(\"filename\",D2))-1),\"Please save the file to show here\")","v":"weighted-average-life-example-financial-model.xlsm"}]
[null,"Sheet Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"]\",CELL(\"filename\",D2))+1,255),\"Please save the file to show here\")","v":"4- Senior Debt"}]
[null,"Description and Purpose of Sheet",null,"Weighted Average Life Calculation Example"]
[null,"Version number",null,1]
[null,"Date",null,{"$f":"TODAY()","v":"2026-05-20"}]
[]
[]
[null,"Senior Debt"]
[]
[null,null,"Inputs"]
[null,null,"Parameter","Value"]
[null,null,"Senior Debt Amount",{"$f":"'3 - Inputs'!D20","v":90000000}]
[null,null,"Senior Debt Interest Rate",{"$f":"'3 - Inputs'!D22","v":0.05}]
[null,null,"Senior Debt Tenor (years)",{"$f":"'3 - Inputs'!D24","v":20}]
[]
[null,null,"Total Annual Debt Service",{"$f":"'3 - Inputs'!G24","v":7221832.8471622188}]
[]
[null,null,"Key Results"]
[null,null,"WAL-Principal",{"$f":"SUMPRODUCT(H26:H50,C26:C50)/SUM(H26:H50)","v":12.097034876276529}]
[null,null,"IRR / YtM",{"$f":"IRR(F25:F50)","v":5.0000000000000044E-2}]
[]
[]
[null,null,"Period","Year","Opening Balance","Payment","Interest","Principal","Closing Balance"]
[null,null,null,null,null,{"$f":"-$D$13","v":-90000000}]
[null,null,1,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2026-04-01"},{"$f":"IF($C26=1,$D$13,I25)","v":90000000},{"$f":"IF(C26<=$D$15,$D$17,0)","v":7221832.8471622188},{"$f":"E26*$D$14","v":4500000},{"$f":"F26-G26","v":2721832.8471622188},{"$f":"E26-H26","v":87278167.152837783}]
[null,null,{"$f":"C26+1","v":2},{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2027-04-01"},{"$f":"IF($A27=1,$C$4,I26)","v":87278167.152837783},{"$f":"IF(C27<=$D$15,$D$17,0)","v":7221832.8471622188},{"$f":"E27*$D$14","v":4363908.3576418897},{"$f":"F27-G27","v":2857924.4895203291},{"$f":"E27-H27","v":84420242.663317457}]
[null,null,{"$f":"C27+1","v":3},{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2028-04-01"},84420242.663317457,7221832.8471622188,4221012.1331658727,3000820.7139963461,81419421.949321106]
[null,null,4,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2029-04-01"},81419421.949321106,7221832.8471622188,4070971.0974660553,3150861.7496961635,78268560.199624941]
[null,null,5,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2030-04-01"},78268560.199624941,7221832.8471622188,3913428.0099812471,{"$f":"F30-G30","v":3308404.8371809716},74960155.362443969]
[null,null,6,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2031-04-01"},74960155.362443969,7221832.8471622188,3748007.7681221985,3473825.0790400202,71486330.283403948]
[null,null,7,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2032-04-01"},71486330.283403948,7221832.8471622188,3574316.5141701978,3647516.332992021,67838813.950411931]
[null,null,8,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2033-04-01"},67838813.950411931,7221832.8471622188,3391940.6975205969,3829892.1496416219,64008921.800770313]
[null,null,9,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2034-04-01"},64008921.800770313,7221832.8471622188,3200446.0900385156,4021386.7571237031,59987535.043646611]
[null,null,10,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2035-04-01"},59987535.043646611,7221832.8471622188,2999376.7521823309,4222456.0949798878,55765078.948666722]
[null,null,11,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2036-04-01"},55765078.948666722,7221832.8471622188,2788253.9474333362,4433578.8997288831,51331500.048937842]
[null,null,12,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2037-04-01"},51331500.048937842,7221832.8471622188,2566575.0024468922,4655257.844715327,46676242.204222515]
[null,null,13,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2038-04-01"},46676242.204222515,7221832.8471622188,2333812.110211126,4888020.7369510923,41788221.467271425]
[null,null,14,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2039-04-01"},41788221.467271425,7221832.8471622188,2089411.0733635714,5132421.7737986473,36655799.69347278]
[null,null,15,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2040-04-01"},36655799.69347278,7221832.8471622188,1832789.9846736391,5389042.8624885799,31266756.830984201]
[null,null,16,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2041-04-01"},31266756.830984201,7221832.8471622188,1563337.8415492103,5658495.0056130085,25608261.825371191]
[null,null,17,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2042-04-01"},25608261.825371191,7221832.8471622188,1280413.0912685597,5941419.7558936588,19666842.069477532]
[null,null,18,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2043-04-01"},19666842.069477532,7221832.8471622188,983342.1034738766,6238490.7436883422,13428351.325789191]
[null,null,19,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2044-04-01"},13428351.325789191,7221832.8471622188,671417.56628945959,6550415.2808727594,6877936.0449164314]
[null,null,20,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2045-04-01"},6877936.0449164314,7221832.8471622188,343896.80224582157,6877936.044916397,3.4458935260772705E-8]
[null,null,21,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2046-04-01"},3.4458935260772705E-8,0,1.7229467630386353E-9,-1.7229467630386353E-9,3.6181882023811342E-8]
[null,null,22,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2047-04-01"},3.6181882023811342E-8,0,1.8090941011905671E-9,-1.8090941011905671E-9,3.7990976125001911E-8]
[null,null,23,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2048-04-01"},3.7990976125001911E-8,0,1.8995488062500958E-9,-1.8995488062500958E-9,3.9890524931252007E-8]
[null,null,24,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2049-04-01"},3.9890524931252007E-8,0,1.9945262465626003E-9,-1.9945262465626003E-9,4.188505117781461E-8]
[null,null,25,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($D$26)))","v":"2050-04-01"},4.188505117781461E-8,0,2.0942525588907306E-9,-2.0942525588907306E-9,4.3979303736705338E-8]
[]
[]
[]
[]
[]
[null,null,null,"Total",null,{"$f":"SUM(F26:F45)","v":144436656.94324437},{"$f":"SUM(G26:G45)","v":54436656.943244405},{"$f":"SUM(H26:H45)","v":89999999.999999985}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"END"]

{"sheet":"5 - Sub Debt & Equity","index":4,"n_rows":54,"n_cols":18,"rows_kept":54,"cut":false,"hidden":false,"merged":[]}
[]
[null,"File Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"[\",CELL(\"filename\",D2))+1,FIND(\"]\",CELL(\"filename\",D2))-FIND(\"[\",CELL(\"filename\",D2))-1),\"Please save the file to show here\")","v":"weighted-average-life-example-financial-model.xlsm"}]
[null,"Sheet Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"]\",CELL(\"filename\",D2))+1,255),\"Please save the file to show here\")","v":"5 - Sub Debt & Equity"}]
[null,"Description and Purpose of Sheet",null,"Weighted Average Life Calculation Example"]
[null,"Version number",null,1]
[null,"Date",null,{"$f":"TODAY()","v":"2026-05-20"}]
[]
[]
[null,"Sub Debt and Equity"]
[]
[null,"Sub-Debt",null,null,"Equity",null,null,"Blended Sub-Debt & Equity"]
[null,"Inputs",null,null,"Inputs",null,null,"Inputs"]
[null,"Parameter","Value",null,"Parameter","Value",null,"Parameter","Value"]
[null,"Subordinated Debt Amount",{"$f":"'3 - Inputs'!D28","v":7999999.9999999991},null,{"$f":"'3 - Inputs'!C34","v":"Ordinary Share Capital"},{"$f":"'3 - Inputs'!D34","v":1999999.9999999991},null,"Total Shareholder Capital",{"$f":"C14+F14","v":9999999.9999999981}]
[null,"Sub Debt Interest Rate (PIK)",{"$f":"'3 - Inputs'!D30","v":0.12}]
[]
[null,"CFADS",{"$f":"'3 - Inputs'!G28","v":8666199.4165946618},null,null,null,null,"Blended Shareholder IRR"]
[]
[null,"Key Results",null,null,"Key Results",null,null,"Key Results"]
[null,"WAL-Principal",{"$f":"SUMPRODUCT($A$28:$A$52,H$28:H$52)/SUM(H$28:H$52)","v":6.1912969695988744},null,"WAL-Principal",{"$f":"SUMPRODUCT($A$28:$A$52,O$28:O$52)/SUM(O$28:O$52)","v":23.34372349760546},null,"WAL-Principal",{"$f":"SUMPRODUCT($A$28:$A$52,R$28:R$52)/SUM(R$28:R$52)","v":19.364869416823737}]
[null,"IRR / YtM",{"$f":"IRR(G26:G42)","v":0.11999999999999988},null,"Equity IRR / YtM",{"$f":"IRR(N27:N52)","v":0.19018440834520534},null,"Equity IRR / YtM",{"$f":"IRR(Q27:Q52)","v":0.1602910926554304}]
[]
[]
[]
[]
["Period","Year","Opening Balance","Change in Balance","Closing Balance","Interest accrued","Payment","WAL Calc",null,"Opening Balance","Change in Balance","Closing Balance","Interest accrued","Payment = Dividends & capital reductions","WAL Calc",null,"Combined Shareholder Cashflow","WAL Calc"]
[null,null,null,null,null,null,{"$f":"-C14","v":-7999999.9999999991},null,null,null,null,null,null,{"$f":"-'3 - Inputs'!D34","v":-1999999.9999999991},null,null,{"$f":"N27+G27","v":-9999999.9999999981}]
[{"$f":"'4- Senior Debt'!C26","v":1},{"$f":"'4- Senior Debt'!D26","v":"2026-04-01"},{"$f":"IF($A28=1,$C$14,E27)","v":7999999.9999999991},{"$f":"-MIN(C28,(G28-F28))","v":-484366.56943244312},{"$f":"C28+D28","v":7515633.4305675561},{"$f":"C28*$C$15","v":959999.99999999988},{"$f":"MIN(C28+F28,$C$17-'4- Senior Debt'!F26)","v":1444366.569432443},{"$f":"MIN(0,D28)","v":-484366.56943244312},null,{"$f":"IF($A28=1,$F$14,L27)","v":1999999.9999999991},{"$f":"-MIN(J28,N28-M28)","v":380368.81669041049},{"$f":"J28+K28","v":2380368.8166904096},{"$f":"J28*$F$21","v":380368.81669041049},{"$f":"$C$17-'4- Senior Debt'!F26-'5 - Sub Debt & Equity'!G28","v":0},{"$f":"MIN(0,K28)","v":0},null,1444366.569432443,{"$f":"H28+O28","v":-484366.56943244312}]
[{"$f":"'4- Senior Debt'!C27","v":2},{"$f":"'4- Senior Debt'!D27","v":"2027-04-01"},{"$f":"IF($A29=1,$C$14,E28)","v":7515633.4305675561},{"$f":"-MIN(C29,(G29-F29))","v":-542490.55776433635},{"$f":"C29+D29","v":6973142.8728032196},{"$f":"C29*$C$15","v":901876.01166810666},{"$f":"MIN(C29+F29,$C$17-'4- Senior Debt'!F27)","v":1444366.569432443},{"$f":"MIN(0,D29)","v":-542490.55776433635},null,{"$f":"IF($A29=1,$F$14,L28)","v":2380368.8166904096},{"$f":"-MIN(J29,N29-M29)","v":452709.03504564206},{"$f":"J29+K29","v":2833077.8517360515},{"$f":"J29*$F$21","v":452709.03504564206},{"$f":"$C$17-'4- Senior Debt'!F27-'5 - Sub Debt & Equity'!G29","v":0},{"$f":"MIN(0,K29)","v":0},null,1444366.569432443,{"$f":"H29+O29","v":-542490.55776433635}]
[{"$f":"'4- Senior Debt'!C28","v":3},{"$f":"'4- Senior Debt'!D28","v":"2028-04-01"},6973142.8728032196,-607589.42469605664,6365553.4481071625,836777.14473638637,{"$f":"MIN(C30+F30,$C$17-'4- Senior Debt'!F28)","v":1444366.569432443},-607589.42469605664,null,{"$f":"IF($A30=1,$F$14,L29)","v":2833077.8517360515},538807.23502832628,3371885.0867643775,{"$f":"J30*$F$21","v":538807.23502832628},{"$f":"$C$17-'4- Senior Debt'!F28-'5 - Sub Debt & Equity'!G30","v":0},0,null,1444366.569432443,-607589.42469605664]
[{"$f":"'4- Senior Debt'!C29","v":4},{"$f":"'4- Senior Debt'!D29","v":"2029-04-01"},6365553.4481071625,-680500.15565958351,5685053.2924475791,763866.41377285949,{"$f":"MIN(C31+F31,$C$17-'4- Senior Debt'!F29)","v":1444366.569432443},-680500.15565958351,null,3371885.0867643775,641279.97023430455,4013165.0569986822,641279.97023430455,{"$f":"$C$17-'4- Senior Debt'!F29-'5 - Sub Debt & Equity'!G31","v":0},0,null,1444366.569432443,-680500.15565958351]
[{"$f":"'4- Senior Debt'!C30","v":5},{"$f":"'4- Senior Debt'!D30","v":"2030-04-01"},5685053.2924475791,-762160.17433873354,4922893.1181088453,682206.39509370946,{"$f":"MIN(C32+F32,$C$17-'4- Senior Debt'!F30)","v":1444366.569432443},-762160.17433873354,null,4013165.0569986822,763241.42195694661,4776406.4789556284,763241.42195694661,{"$f":"$C$17-'4- Senior Debt'!F30-'5 - Sub Debt & Equity'!G32","v":0},0,null,1444366.569432443,-762160.17433873354]
[{"$f":"'4- Senior Debt'!C31","v":6},{"$f":"'4- Senior Debt'!D31","v":"2031-04-01"},4922893.1181088453,-853619.39525938162,4069273.7228494636,590747.17417306139,{"$f":"MIN(C33+F33,$C$17-'4- Senior Debt'!F31)","v":1444366.569432443},-853619.39525938162,null,4776406.4789556284,908398.04021638166,5684804.51917201,908398.04021638166,{"$f":"$C$17-'4- Senior Debt'!F31-'5 - Sub Debt & Equity'!G33","v":0},0,null,1444366.569432443,-853619.39525938162]
[{"$f":"'4- Senior Debt'!C32","v":7},{"$f":"'4- Senior Debt'!D32","v":"2032-04-01"},4069273.7228494636,-956053.72269050742,3113220.0001589563,488312.84674193559,{"$f":"MIN(C34+F34,$C$17-'4- Senior Debt'!F32)","v":1444366.569432443},-956053.72269050742,null,5684804.51917201,1081161.1840368782,6765965.7032088879,1081161.1840368782,{"$f":"$C$17-'4- Senior Debt'!F32-'5 - Sub Debt & Equity'!G34","v":0},0,null,1444366.569432443,-956053.72269050742]
[{"$f":"'4- Senior Debt'!C33","v":8},{"$f":"'4- Senior Debt'!D33","v":"2033-04-01"},3113220.0001589563,-1070780.1694133682,2042439.8307455881,373586.40001907473,{"$f":"MIN(C35+F35,$C$17-'4- Senior Debt'!F33)","v":1444366.569432443},-1070780.1694133682,null,6765965.7032088879,1286781.1841487335,8052746.8873576215,1286781.1841487335,{"$f":"$C$17-'4- Senior Debt'!F33-'5 - Sub Debt & Equity'!G35","v":0},0,null,1444366.569432443,-1070780.1694133682]
[{"$f":"'4- Senior Debt'!C34","v":9},{"$f":"'4- Senior Debt'!D34","v":"2034-04-01"},2042439.8307455881,-1199273.7897429725,843166.04100261559,245092.77968947057,{"$f":"MIN(C36+F36,$C$17-'4- Senior Debt'!F34)","v":1444366.569432443},-1199273.7897429725,null,8052746.8873576215,1531506.9023258032,9584253.7896834239,1531506.9023258032,{"$f":"$C$17-'4- Senior Debt'!F34-'5 - Sub Debt & Equity'!G36","v":0},0,null,1444366.569432443,-1199273.7897429725]
[{"$f":"'4- Senior Debt'!C35","v":10},{"$f":"'4- Senior Debt'!D35","v":"2035-04-01"},843166.04100261559,-843166.04100261559,0,101179.92492031386,{"$f":"MIN(C37+F37,$C$17-'4- Senior Debt'!F35)","v":944345.96592292946},-843166.04100261559,null,9584253.7896834239,1322755.0329117207,10907008.822595146,1822775.6364212341,{"$f":"$C$17-'4- Senior Debt'!F35-'5 - Sub Debt & Equity'!G37","v":500020.60350951354},0,null,1444366.569432443,-843166.04100261559]
[{"$f":"'4- Senior Debt'!C36","v":11},{"$f":"'4- Senior Debt'!D36","v":"2036-04-01"},0,0,0,0,{"$f":"MIN(C38+F38,$C$17-'4- Senior Debt'!F36)","v":0},0,null,10907008.822595146,629976.45030874945,11536985.272903895,2074343.0197411925,{"$f":"$C$17-'4- Senior Debt'!F36-'5 - Sub Debt & Equity'!G38","v":1444366.569432443},0,null,1444366.569432443,0]
[{"$f":"'4- Senior Debt'!C37","v":12},{"$f":"'4- Senior Debt'!D37","v":"2037-04-01"},0,0,0,0,{"$f":"MIN(C39+F39,$C$17-'4- Senior Debt'!F37)","v":0},0,null,11536985.272903895,749788.14878213173,12286773.421686027,2194154.7182145747,{"$f":"$C$17-'4- Senior Debt'!F37-'5 - Sub Debt & Equity'!G39","v":1444366.569432443},0,null,1444366.569432443,0]
[{"$f":"'4- Senior Debt'!C38","v":13},{"$f":"'4- Senior Debt'!D38","v":"2038-04-01"},0,0,0,0,{"$f":"MIN(C40+F40,$C$17-'4- Senior Debt'!F38)","v":0},0,null,12286773.421686027,892386.16424250836,13179159.585928535,2336752.7336749514,{"$f":"$C$17-'4- Senior Debt'!F38-'5 - Sub Debt & Equity'!G40","v":1444366.569432443},0,null,1444366.569432443,0]
[{"$f":"'4- Senior Debt'!C39","v":14},{"$f":"'4- Senior Debt'!D39","v":"2039-04-01"},0,0,0,0,{"$f":"MIN(C41+F41,$C$17-'4- Senior Debt'!F39)","v":0},0,null,13179159.585928535,1062104.0989044169,14241263.684832953,2506470.6683368599,{"$f":"$C$17-'4- Senior Debt'!F39-'5 - Sub Debt & Equity'!G41","v":1444366.569432443},0,null,1444366.569432443,0]
[{"$f":"'4- Senior Debt'!C40","v":15},{"$f":"'4- Senior Debt'!D40","v":"2040-04-01"},0,0,0,0,{"$f":"MIN(C42+F42,$C$17-'4- Senior Debt'!F40)","v":0},0,null,14241263.684832953,1264099.7385555711,15505363.423388524,2708466.3079880141,{"$f":"$C$17-'4- Senior Debt'!F40-'5 - Sub Debt & Equity'!G42","v":1444366.569432443},0,null,1444366.569432443,0]
[{"$f":"'4- Senior Debt'!C41","v":16},{"$f":"'4- Senior Debt'!D41","v":"2041-04-01"},0,0,0,0,{"$f":"MIN(C43+F43,$C$17-'4- Senior Debt'!F41)","v":0},0,null,15505363.423388524,1504511.7994220909,17009875.222810615,2948878.3688545339,{"$f":"$C$17-'4- Senior Debt'!F41-'5 - Sub Debt & Equity'!G43","v":1444366.569432443},0,null,1444366.569432443,0]
[{"$f":"'4- Senior Debt'!C42","v":17},{"$f":"'4- Senior Debt'!D42","v":"2042-04-01"},0,0,0,0,{"$f":"MIN(C44+F44,$C$17-'4- Senior Debt'!F42)","v":0},0,null,17009875.222810615,1790646.4858435616,18800521.708654176,3235013.0552760046,{"$f":"$C$17-'4- Senior Debt'!F42-'5 - Sub Debt & Equity'!G44","v":1444366.569432443},0,null,1444366.569432443,0]
[{"$f":"'4- Senior Debt'!C43","v":18},{"$f":"'4- Senior Debt'!D43","v":"2043-04-01"},0,0,0,0,{"$f":"MIN(C45+F45,$C$17-'4- Senior Debt'!F43)","v":0},0,null,18800521.708654176,2131199.5283091404,20931721.236963317,3575566.0977415834,{"$f":"$C$17-'4- Senior Debt'!F43-'5 - Sub Debt & Equity'!G45","v":1444366.569432443},0,null,1444366.569432443,0]
[{"$f":"'4- Senior Debt'!C44","v":19},{"$f":"'4- Senior Debt'!D44","v":"2044-04-01"},0,0,0,0,{"$f":"MIN(C46+F46,$C$17-'4- Senior Debt'!F44)","v":0},0,null,20931721.236963317,2536520.4496661951,23468241.686629511,3980887.0190986381,{"$f":"$C$17-'4- Senior Debt'!F44-'5 - Sub Debt & Equity'!G46","v":1444366.569432443},0,null,1444366.569432443,0]
[{"$f":"'4- Senior Debt'!C45","v":20},{"$f":"'4- Senior Debt'!D45","v":"2045-04-01"},0,0,0,0,{"$f":"MIN(C47+F47,$C$17-'4- Senior Debt'!F45)","v":0},0,null,23468241.686629511,3018927.0906414744,26487168.777270988,4463293.6600739174,{"$f":"$C$17-'4- Senior Debt'!F45-'5 - Sub Debt & Equity'!G47","v":1444366.569432443},0,null,1444366.569432443,0]
[{"$f":"'4- Senior Debt'!C46","v":21},{"$f":"'4- Senior Debt'!D46","v":"2046-04-01"},0,0,0,0,{"$f":"MIN(C48+F48,$C$17-'4- Senior Debt'!F46)","v":0},0,null,26487168.777270988,-3628752.8939497834,22858415.883321203,5037446.5226448784,{"$f":"$C$17-'4- Senior Debt'!F46-'5 - Sub Debt & Equity'!G48","v":8666199.4165946618},-3628752.8939497834,null,8666199.4165946618,-3628752.8939497834]
[{"$f":"'4- Senior Debt'!C47","v":22},{"$f":"'4- Senior Debt'!D47","v":"2047-04-01"},0,0,0,0,{"$f":"MIN(C49+F49,$C$17-'4- Senior Debt'!F47)","v":0},0,null,22858415.883321203,-4318885.116116575,18539530.767204627,4347314.3004780868,{"$f":"$C$17-'4- Senior Debt'!F47-'5 - Sub Debt & Equity'!G49","v":8666199.4165946618},-4318885.116116575,null,8666199.4165946618,-4318885.116116575]
[{"$f":"'4- Senior Debt'!C48","v":23},{"$f":"'4- Senior Debt'!D48","v":"2048-04-01"},0,0,0,0,{"$f":"MIN(C50+F50,$C$17-'4- Senior Debt'!F48)","v":0},0,null,18539530.767204627,-5140269.7266361192,13399261.040568508,3525929.6899585431,{"$f":"$C$17-'4- Senior Debt'!F48-'5 - Sub Debt & Equity'!G50","v":8666199.4165946618},-5140269.7266361192,null,8666199.4165946618,-5140269.7266361192]
[{"$f":"'4- Senior Debt'!C49","v":24},{"$f":"'4- Senior Debt'!D49","v":"2049-04-01"},0,0,0,0,{"$f":"MIN(C51+F51,$C$17-'4- Senior Debt'!F49)","v":0},0,null,13399261.040568508,-6117868.8833311796,7281392.1572373286,2548330.5332634822,{"$f":"$C$17-'4- Senior Debt'!F49-'5 - Sub Debt & Equity'!G51","v":8666199.4165946618},-6117868.8833311796,null,8666199.4165946618,-6117868.8833311796]
[{"$f":"'4- Senior Debt'!C50","v":25},{"$f":"'4- Senior Debt'!D50","v":"2050-04-01"},0,0,0,0,{"$f":"MIN(C52+F52,$C$17-'4- Senior Debt'!F50)","v":0},0,null,7281392.1572373286,-7281392.1572373286,0,1384807.2593535997,{"$f":"$C$17-'4- Senior Debt'!F50-'5 - Sub Debt & Equity'!G52","v":8666199.4165946618},-7281392.1572373286,null,8666199.4165946618,-7281392.1572373286]
[]
["END"]

{"sheet":"6- Single Asset","index":5,"n_rows":63,"n_cols":14,"rows_kept":63,"cut":false,"hidden":false,"merged":[[60,2,60,15]]}
[]
[null,"File Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"[\",CELL(\"filename\",D2))+1,FIND(\"]\",CELL(\"filename\",D2))-FIND(\"[\",CELL(\"filename\",D2))-1),\"Please save the file to show here\")","v":"weighted-average-life-example-financial-model.xlsm"}]
[null,"Sheet Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"]\",CELL(\"filename\",D2))+1,255),\"Please save the file to show here\")","v":"6- Single Asset"}]
[null,"Description and Purpose of Sheet",null,"Weighted Average Life Calculation Example"]
[null,"Version number",null,1]
[null,"Date",null,{"$f":"TODAY()","v":"2026-05-20"}]
[]
[]
[null,"Single Asset"]
[]
[]
[]
[null,null,"Treat the CFADS as a Single Asset",null,null,"Aggregate of the Three Instruments"]
[null,null,"Inputs"]
[null,null,"Parameter","Value"]
[null,null,"Total Capital",{"$f":"'3 - Inputs'!D20+'3 - Inputs'!D28+'3 - Inputs'!D34","v":100000000}]
[null,null,"CFADS",{"$f":"'3 - Inputs'!G28","v":8666199.4165946618}]
[]
[]
[]
[]
[null,null,"Key Results"]
[null,null,"WAL-Principal",{"$f":"SUMPRODUCT(G30:G54,B30:B54)/SUM(G30:G54)","v":16.407339080397929},null,"WAL-Principal",{"$f":"SUMPRODUCT($B$30:$B$54,L$30:L$54)/SUM(L$30:L$54)","v":14.341588715042414}]
[null,null,"IRR / YtM",{"$f":"IRR(E29:E54)","v":7.1099271395101304E-2}]
[]
[]
[]
[null,"Period","Year","Opening Balance","Payment","Interest","Principal","Closing Balance",null,"Total Payment","Total \"Interest\"","Total Net \"Principal\"",null,"Delta between \"Whole Asset\" and Sum-of-the-Liabilities"]
[null,null,null,null,{"$f":"-$D$16","v":-100000000}]
[null,1,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2026-04-01"},{"$f":"IF($B30=1,$D$16,H29)","v":100000000},{"$f":"$D$17","v":8666199.4165946618},{"$f":"D30*$D$24","v":7109927.1395101305},{"$f":"E30-F30","v":1556272.2770845313},{"$f":"D30-G30","v":98443727.722915471},null,{"$f":"L30+K30","v":8666199.4165946618},{"$f":"'4- Senior Debt'!G26+'5 - Sub Debt & Equity'!F28+'5 - Sub Debt & Equity'!M28","v":5840368.8166904105},{"$f":"'4- Senior Debt'!H26+'5 - Sub Debt & Equity'!D28*-1+'5 - Sub Debt & Equity'!K28*-1","v":2825830.5999042513},null,{"$f":"G30-L30","v":-1269558.32281972}]
[null,{"$f":"B30+1","v":2},{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2027-04-01"},{"$f":"IF($A31=1,$C$4,H30)","v":98443727.722915471},{"$f":"$D$17","v":8666199.4165946618},{"$f":"D31*$D$24","v":6999277.3145170249},{"$f":"E31-F31","v":1666922.1020776369},{"$f":"D31-G31","v":96776805.620837837},null,8666199.4165946618,{"$f":"'4- Senior Debt'!G27+'5 - Sub Debt & Equity'!F29+'5 - Sub Debt & Equity'!M29","v":5718493.4043556387},{"$f":"'4- Senior Debt'!H27+'5 - Sub Debt & Equity'!D29*-1+'5 - Sub Debt & Equity'!K29*-1","v":2947706.0122390236},null,{"$f":"G31-L31","v":-1280783.9101613867}]
[null,{"$f":"B31+1","v":3},{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2028-04-01"},{"$f":"IF($A32=1,$C$4,H31)","v":96776805.620837837},8666199.4165946618,6880760.3675869144,1785439.0490077473,94991366.571830094,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G28+'5 - Sub Debt & Equity'!F30+'5 - Sub Debt & Equity'!M30","v":5596596.5129305851},{"$f":"'4- Senior Debt'!H28+'5 - Sub Debt & Equity'!D30*-1+'5 - Sub Debt & Equity'!K30*-1","v":3069602.9036640767},null,-1284163.8546563294]
[null,4,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2029-04-01"},94991366.571830094,8666199.4165946618,6753816.9520821013,1912382.4645125605,93078984.107317537,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G29+'5 - Sub Debt & Equity'!F31+'5 - Sub Debt & Equity'!M31","v":5476117.4814732196},{"$f":"'4- Senior Debt'!H29+'5 - Sub Debt & Equity'!D31*-1+'5 - Sub Debt & Equity'!K31*-1","v":3190081.9351214422},null,-1277699.4706088817]
[null,5,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2030-04-01"},93078984.107317537,8666199.4165946618,6617847.9522264907,{"$f":"E34-F34","v":2048351.4643681711},91030632.642949373,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G30+'5 - Sub Debt & Equity'!F32+'5 - Sub Debt & Equity'!M32","v":5358875.827031903},{"$f":"'4- Senior Debt'!H30+'5 - Sub Debt & Equity'!D32*-1+'5 - Sub Debt & Equity'!K32*-1","v":3307323.5895627583},null,-1258972.1251945873]
[null,6,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2031-04-01"},91030632.642949373,8666199.4165946618,6472211.6555488249,2193987.7610458368,88836644.881903529,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G31+'5 - Sub Debt & Equity'!F33+'5 - Sub Debt & Equity'!M33","v":5247152.9825116415},{"$f":"'4- Senior Debt'!H31+'5 - Sub Debt & Equity'!D33*-1+'5 - Sub Debt & Equity'!K33*-1","v":3419046.4340830203},null,-1225058.6730371835]
[null,7,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2032-04-01"},88836644.881903529,8666199.4165946618,6316220.7242886964,2349978.6923059653,86486666.189597562,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G32+'5 - Sub Debt & Equity'!F34+'5 - Sub Debt & Equity'!M34","v":5143790.5449490119},{"$f":"'4- Senior Debt'!H32+'5 - Sub Debt & Equity'!D34*-1+'5 - Sub Debt & Equity'!K34*-1","v":3522408.8716456508},null,-1172430.1793396855]
[null,8,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2033-04-01"},86486666.189597562,8666199.4165946618,6149138.9514717292,2517060.4651229326,83969605.724474624,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G33+'5 - Sub Debt & Equity'!F35+'5 - Sub Debt & Equity'!M35","v":5052308.2816884052},{"$f":"'4- Senior Debt'!H33+'5 - Sub Debt & Equity'!D35*-1+'5 - Sub Debt & Equity'!K35*-1","v":3613891.1349062566},null,-1096830.669783324]
[null,9,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2034-04-01"},83969605.724474624,8666199.4165946618,5970177.7863440737,2696021.6302505881,81273584.094224036,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G34+'5 - Sub Debt & Equity'!F36+'5 - Sub Debt & Equity'!M36","v":4977045.7720537893},{"$f":"'4- Senior Debt'!H34+'5 - Sub Debt & Equity'!D36*-1+'5 - Sub Debt & Equity'!K36*-1","v":3689153.6445408724},null,-993132.01429028437]
[null,10,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2035-04-01"},81273584.094224036,8666199.4165946618,5778492.612767823,2887706.8038268387,78385877.290397197,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G35+'5 - Sub Debt & Equity'!F37+'5 - Sub Debt & Equity'!M37","v":4923332.3135238793},{"$f":"'4- Senior Debt'!H35+'5 - Sub Debt & Equity'!D37*-1+'5 - Sub Debt & Equity'!K37*-1","v":3742867.1030707825},null,-855160.29924394377]
[null,11,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2036-04-01"},78385877.290397197,8666199.4165946618,5573178.7630130583,3093020.6535816034,75292856.636815593,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G36+'5 - Sub Debt & Equity'!F38+'5 - Sub Debt & Equity'!M38","v":4862596.9671745282},{"$f":"'4- Senior Debt'!H36+'5 - Sub Debt & Equity'!D38*-1+'5 - Sub Debt & Equity'!K38*-1","v":3803602.4494201336},null,-710581.79583853018]
[null,12,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2037-04-01"},75292856.636815593,8666199.4165946618,5353267.248133406,3312932.1684612557,71979924.468354344,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G37+'5 - Sub Debt & Equity'!F39+'5 - Sub Debt & Equity'!M39","v":4760729.7206614669},{"$f":"'4- Senior Debt'!H37+'5 - Sub Debt & Equity'!D39*-1+'5 - Sub Debt & Equity'!K39*-1","v":3905469.6959331953},null,-592537.52747193957]
[null,13,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2038-04-01"},71979924.468354344,8666199.4165946618,5117720.1847744184,3548479.2318202434,68431445.236534104,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G38+'5 - Sub Debt & Equity'!F40+'5 - Sub Debt & Equity'!M40","v":4670564.8438860774},{"$f":"'4- Senior Debt'!H38+'5 - Sub Debt & Equity'!D40*-1+'5 - Sub Debt & Equity'!K40*-1","v":3995634.5727085839},null,-447155.34088834049]
[null,14,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2039-04-01"},68431445.236534104,8666199.4165946618,4865425.8968313504,3800773.5197633114,64630671.716770791,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G39+'5 - Sub Debt & Equity'!F41+'5 - Sub Debt & Equity'!M41","v":4595881.7417004313},{"$f":"'4- Senior Debt'!H39+'5 - Sub Debt & Equity'!D41*-1+'5 - Sub Debt & Equity'!K41*-1","v":4070317.6748942304},null,-269544.15513091907]
[null,15,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2040-04-01"},64630671.716770791,8666199.4165946618,4595193.6688383846,4071005.7477562772,60559665.969014511,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G40+'5 - Sub Debt & Equity'!F42+'5 - Sub Debt & Equity'!M42","v":4541256.2926616529},{"$f":"'4- Senior Debt'!H40+'5 - Sub Debt & Equity'!D42*-1+'5 - Sub Debt & Equity'!K42*-1","v":4124943.1239330089},null,-53937.376176731661]
[null,16,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2041-04-01"},60559665.969014511,8666199.4165946618,4305748.1263276432,4360451.2902670186,56199214.67874749,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G41+'5 - Sub Debt & Equity'!F43+'5 - Sub Debt & Equity'!M43","v":4512216.2104037441},{"$f":"'4- Senior Debt'!H41+'5 - Sub Debt & Equity'!D43*-1+'5 - Sub Debt & Equity'!K43*-1","v":4153983.2061909176},null,206468.08407610096]
[null,17,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2042-04-01"},56199214.67874749,8666199.4165946618,3995723.2166358288,4670476.1999588329,51528738.478788659,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G42+'5 - Sub Debt & Equity'!F44+'5 - Sub Debt & Equity'!M44","v":4515426.1465445645},{"$f":"'4- Senior Debt'!H42+'5 - Sub Debt & Equity'!D44*-1+'5 - Sub Debt & Equity'!K44*-1","v":4150773.2700500973},null,519702.92990873568]
[null,18,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2043-04-01"},51528738.478788659,8666199.4165946618,3663655.7617505942,5002543.654844068,46526194.823944591,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G43+'5 - Sub Debt & Equity'!F45+'5 - Sub Debt & Equity'!M45","v":4558908.20121546},{"$f":"'4- Senior Debt'!H43+'5 - Sub Debt & Equity'!D45*-1+'5 - Sub Debt & Equity'!K45*-1","v":4107291.2153792018},null,895252.43946486618]
[null,19,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2044-04-01"},46526194.823944591,8666199.4165946618,3307978.5527689941,5358220.8638256676,41167973.96011892,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G44+'5 - Sub Debt & Equity'!F46+'5 - Sub Debt & Equity'!M46","v":4652304.5853880979},{"$f":"'4- Senior Debt'!H44+'5 - Sub Debt & Equity'!D46*-1+'5 - Sub Debt & Equity'!K46*-1","v":4013894.8312065643},null,1344326.0326191033]
[null,20,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2045-04-01"},41167973.96011892,8666199.4165946618,2927012.9533769586,5739186.4632177036,35428787.496901214,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G45+'5 - Sub Debt & Equity'!F47+'5 - Sub Debt & Equity'!M47","v":4807190.4623197392},{"$f":"'4- Senior Debt'!H45+'5 - Sub Debt & Equity'!D47*-1+'5 - Sub Debt & Equity'!K47*-1","v":3859008.9542749226},null,1880177.508942781]
[null,21,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2046-04-01"},35428787.496901214,8666199.4165946618,2518960.9774415512,6147238.4391531106,29281549.057748102,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G46+'5 - Sub Debt & Equity'!F48+'5 - Sub Debt & Equity'!M48","v":5037446.5226448802},{"$f":"'4- Senior Debt'!H46+'5 - Sub Debt & Equity'!D48*-1+'5 - Sub Debt & Equity'!K48*-1","v":3628752.8939497815},null,2518485.5452033291]
[null,22,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2047-04-01"},29281549.057748102,8666199.4165946618,2081896.8033258051,6584302.6132688569,22697246.444479246,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G47+'5 - Sub Debt & Equity'!F49+'5 - Sub Debt & Equity'!M49","v":4347314.3004780887},{"$f":"'4- Senior Debt'!H47+'5 - Sub Debt & Equity'!D49*-1+'5 - Sub Debt & Equity'!K49*-1","v":4318885.1161165731},null,2265417.4971522838]
[null,23,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2048-04-01"},22697246.444479246,8666199.4165946618,1613757.6848775281,7052441.7317171339,15644804.712762112,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G48+'5 - Sub Debt & Equity'!F50+'5 - Sub Debt & Equity'!M50","v":3525929.6899585449},{"$f":"'4- Senior Debt'!H48+'5 - Sub Debt & Equity'!D50*-1+'5 - Sub Debt & Equity'!K50*-1","v":5140269.7266361173},null,1912172.0050810166]
[null,24,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2049-04-01"},15644804.712762112,8666199.4165946618,1112334.2161960334,7553865.2003986286,8090939.5123634832,null,8666199.4165946618,{"$f":"'4- Senior Debt'!G49+'5 - Sub Debt & Equity'!F51+'5 - Sub Debt & Equity'!M51","v":2548330.533263484},{"$f":"'4- Senior Debt'!H49+'5 - Sub Debt & Equity'!D51*-1+'5 - Sub Debt & Equity'!K51*-1","v":6117868.8833311778},null,1435996.3170674508]
[null,25,{"$f":"EDATE('3 - Inputs'!$D$13, 12*(ROW()-ROW($B$30)))","v":"2050-04-01"},8090939.5123634832,8666199.4165946618,575259.90423087985,8090939.5123637822,-2.989545464515686E-7,null,8666199.416590929,{"$f":"'4- Senior Debt'!G50+'5 - Sub Debt & Equity'!F52+'5 - Sub Debt & Equity'!M52","v":1384807.2593536018},{"$f":"'4- Senior Debt'!H50+'5 - Sub Debt & Equity'!D52*-1+'5 - Sub Debt & Equity'!K52*-1","v":7281392.1572373267},null,809547.35512645543]
[]
[null,null,"Total",null,{"$f":"SUM(E30:E54)","v":216654985.41486642},{"$f":"SUM(F30:F54)","v":116654985.41486624},{"$f":"SUM(G30:G54)","v":100000000.0000003},null,null,{"$f":"SUM(J30:J54)","v":216654985.41486269},{"$f":"SUM(K30:K54)","v":116654985.41486284},{"$f":"SUM(L30:L54)","v":99999999.99999994},null,{"$f":"SUM(N30:N54)","v":3.3713877201080322E-7}]
[]
[]
[]
[]
[]
[]
[null,"END"]

{"sheet":"7- GDP Deflator","index":6,"n_rows":128,"n_cols":5,"rows_kept":128,"cut":false,"hidden":false,"merged":[]}
[]
[null,"File Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"[\",CELL(\"filename\",D2))+1,FIND(\"]\",CELL(\"filename\",D2))-FIND(\"[\",CELL(\"filename\",D2))-1),\"Please save the file to show here\")","v":"weighted-average-life-example-financial-model.xlsm"}]
[null,"Sheet Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"]\",CELL(\"filename\",D2))+1,255),\"Please save the file to show here\")","v":"7- GDP Deflator"}]
[null,"Description and Purpose of Sheet",null,"Weighted Average Life Calculation Example"]
[null,"Version number",null,1]
[null,"Date",null,{"$f":"TODAY()","v":"2026-05-20"}]
[]
[]
[null,"GDP Deflator"]
[null,"GDP Deflator Series - Base Year = 2023. "]
[]
[null,"Taken from TAG Data Book here: ",null,"TAG Data Book"]
[null,null,"(2023=100)"]
[]
[null,null,"Year","GDP Deflator","YoY % change"]
[null,null,1990,44.634926634359687]
[null,null,1991,47.634346188631369,{"$f":"(D17-D16)/(D16)*100","v":6.7198935462408667}]
[null,null,1992,49.198849907533649,{"$f":"((D18-D17)/D17)*100","v":3.2844026297891578}]
[null,null,1993,50.675404157403591,3.0011966796887299]
[null,null,1994,51.611905056108021,1.8480383418266422]
[null,null,1995,52.959494441115908,2.6110049290815809]
[null,null,1996,55.150443433538967,4.1370277710243455]
[null,null,1997,55.071943081014233,{"$f":"((D23-D22)/D22)*100","v":-0.14233856998690217}]
[null,null,1998,55.778238580846526,1.2824960593696308]
[null,null,1999,56.548705003774458,1.3813028925450852]
[null,null,2000,57.184391720912551,1.1241401851654471]
[null,null,2001,58.04831094446515,1.5107605371915866]
[null,null,2002,59.27586539889289,2.1147117538047597]
[null,null,2003,60.690636963905419,2.386758178040794]
[null,null,2004,62.261474705961149,2.5882703175284765]
[null,null,2005,64.092734252424322,2.9412402374205908]
[null,null,2006,65.88453594973484,2.7956393469712881]
[null,null,2007,67.364309129404091,2.2460098691416941]
[null,null,2008,69.691283864958621,3.4543139618406915]
[null,null,2009,70.998584709252754,1.8758455459470378]
[null,null,2010,72.125210139005858,1.5868280112438349]
[null,null,2011,73.710065801155281,2.1973671329275208]
[null,null,2012,74.846244183861117,1.5414155045945277]
[null,null,2013,76.423935131295991,2.1079093074586996]
[null,null,2014,77.431667831365459,1.3186087556708348]
[null,null,2015,77.943269996563018,0.66071438150053963]
[null,null,2016,79.471638632557088,1.9608731274187818]
[null,null,2017,80.947362190865917,1.8569184978454316]
[null,null,2018,82.502935975486295,1.9217102849534349]
[null,null,2019,84.261510010352509,2.1315290347833571]
[null,null,2020,88.639462210283355,5.1956726142137297]
[null,null,2021,88.514027784555992,-0.14151081538580332]
[null,null,2022,93.323524250482578,5.4335980254259209]
[null,null,2023,100,7.1541187531641013]
[null,null,2024,103.83644513853338,3.836445138533378]
[null,null,2025,107.35218079199443,3.3858397682726249]
[null,null,2026,109.76846895421627,2.2508049155551353]
[null,null,2027,112.02379237117708,2.0546186336091532]
[null,null,2028,114.18299879546288,1.9274534262610332]
[null,null,2029,116.26899816682132,1.8268913878283326]
[null,null,2030,118.51378524529244,1.9306841151674217]
[null,null,2031,121.07411999722073,2.1603687255698345]
[null,null,2032,123.8588247571568,2.2999999999999878]
[null,null,2033,126.7075777265714,2.2999999999999989]
[null,null,2034,129.62185201428252,2.2999999999999852]
[null,null,2035,132.603154610611,2.2999999999999829]
[null,null,2036,135.65302716665505,2.299999999999998]
[null,null,2037,138.7730467914881,2.2999999999999869]
[null,null,2038,141.96482686769235,2.3000000000000194]
[null,null,2039,145.23001788564923,2.2999999999999692]
[null,null,2040,148.57030829701912,2.2999999999999727]
[null,null,2041,151.98742538785055,2.2999999999999901]
[null,null,2042,155.48313617177109,2.2999999999999874]
[null,null,2043,159.05924830372183,2.3000000000000012]
[null,null,2044,162.71761101470739,2.2999999999999714]
[null,null,2045,166.46011606804564,2.2999999999999869]
[null,null,2046,170.2886987376107,2.3000000000000096]
[null,null,2047,174.20533880857573,2.2999999999999909]
[null,null,2048,178.21206160117296,2.2999999999999932]
[null,null,2049,182.31093901799991,2.2999999999999852]
[null,null,2050,186.50409061541387,2.2999999999999785]
[null,null,2051,190.79368469956839,2.299999999999998]
[null,null,2052,195.18193944765844,2.2999999999999869]
[null,null,2053,199.67112405495456,2.2999999999999901]
[null,null,2054,204.2635599082185,2.2999999999999905]
[null,null,2055,208.96162178610754,2.30000000000001]
[null,null,2056,213.76773908718798,{"$f":"((D82-D81)/D81)*100","v":2.2999999999999812}]
[null,null,2057,218.68439708619331,2.3000000000000069]
[null,null,2058,223.71413821917571,2.2999999999999763]
[null,null,2059,228.85956339821672,2.2999999999999869]
[null,null,2060,234.1233333563757,2.2999999999999954]
[null,null,2061,239.50817002357229,2.2999999999999803]
[null,null,2062,245.01685793411446,2.3000000000000007]
[null,null,2063,250.65224566659907,2.2999999999999936]
[null,null,2064,256.41724731693085,2.3000000000000016]
[null,null,2065,262.31484400522021,2.2999999999999785]
[null,null,2066,268.34808541734026,2.2999999999999936]
[null,null,2067,274.52009138193904,2.2999999999999838]
[null,null,2068,280.83405348372361,2.2999999999999892]
[null,null,2069,287.29323671384918,2.2999999999999758]
[null,null,2070,293.90098115826771,2.2999999999999994]
[null,null,2071,300.66070372490782,2.2999999999999829]
[null,null,2072,307.57589991058069,2.2999999999999963]
[null,null,2073,314.65014560852399,2.2999999999999807]
[null,null,2074,321.88709895752004,2.3000000000000003]
[null,null,2075,329.29050223354295,2.2999999999999856]
[null,null,2076,336.86418378491442,2.2999999999999932]
[null,null,2077,344.61206001196734,2.2999999999999692]
[null,null,2078,352.53813739224262,2.3000000000000074]
[null,null,2079,360.64651455226414,2.2999999999999825]
[null,null,2080,368.94138438696615,2.2999999999999825]
[null,null,2081,377.42703622786632,2.2999999999999874]
[null,null,2082,386.10785806110727,2.3000000000000043]
[null,null,2083,394.98833879651272,2.2999999999999972]
[null,null,2084,404.07307058883248,2.2999999999999914]
[null,null,2085,413.36675121237556,2.2999999999999838]
[null,null,2086,422.87418649026023,2.3000000000000074]
[null,null,2087,432.60029277953612,2.2999999999999776]
[null,null,2088,442.5500995134654,2.2999999999999883]
[null,null,2089,452.72875180227504,2.2999999999999865]
[null,null,2090,463.14151309372738,2.3000000000000034]
[null,null,2091,473.7937678948831,2.299999999999998]
[null,null,2092,484.69102455646532,2.2999999999999807]
[null,null,2093,495.83891812126399,2.2999999999999923]
[null,null,2094,507.24321323805299,2.2999999999999856]
[null,null,2095,518.90980714252817,2.2999999999999918]
[null,null,2096,530.84473270680621,2.2999999999999807]
[null,null,2097,543.05416155906278,2.3000000000000043]
[null,null,2098,555.54440727492101,2.2999999999999616]
[null,null,2099,568.3219286422443,2.3000000000000189]
[null,null,2100,581.39333300101578,2.2999999999999745]
[]
["END"]

{"sheet":"8 - Real Gilt","index":7,"n_rows":24,"n_cols":4,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
[]
[null,"File Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"[\",CELL(\"filename\",D2))+1,FIND(\"]\",CELL(\"filename\",D2))-FIND(\"[\",CELL(\"filename\",D2))-1),\"Please save the file to show here\")","v":"weighted-average-life-example-financial-model.xlsm"}]
[null,"Sheet Name",null,{"$f":"IFERROR(MID(CELL(\"filename\",D2),FIND(\"]\",CELL(\"filename\",D2))+1,255),\"Please save the file to show here\")","v":"8 - Real Gilt"}]
[null,"Description and Purpose of Sheet",null,"Weighted Average Life Calculation Example"]
[null,"Version number",null,1]
[null,"Date",null,{"$f":"TODAY()","v":"2026-05-20"}]
[]
[]
[null,"Real Gilt"]
[]
[null,"Purpose: To ensure gilt rates are net of inflation, i.e., to go from nominal to real rates. Using real rates from the DMO/BoE is not consistent  given they use index-linked measures which are linked to RPI, and appraisal methods use GDP deflation instead."]
[null,"Its important we ensure consistency across appraisal by using the same deflator data which will be used for inflation adjustments across the rest of the appraisal modelling."]
[]
[null,"Start of the appraisal period",2023]
[null,"End of the appraisal period",2060]
[null,"Length of the appraisal period",{"$f":"C15-C14","v":37}]
[null,"CAGR Appraisal period",{"$f":"((_xlfn.XLOOKUP(C15,'7- GDP Deflator'!$C$16:$C$126,'7- GDP Deflator'!$D$16:$D$126,,0)/_xlfn.XLOOKUP(C14,'7- GDP Deflator'!$C$16:$C$126,'7- GDP Deflator'!$D$16:$D$126,,0))^(1/C16)-1)*100","v":2.3257630423085374}]
[null,"WAL ",{"$f":"'2 - WAL Summary'!F17","v":16.407339080397929}]
[null,"Nearest liquidity benchmark",{"$f":"_xlfn.LET(_xlpm.x,$C$18,_xlpm.b,{5,10,30},INDEX(_xlpm.b,_xlfn.XMATCH(MIN(ABS(_xlpm.b-_xlpm.x)),ABS(_xlpm.b-_xlpm.x),0)))","v":10}]
[null,"Corresponding gilt rate (read off curve using a benchmark maturity)",4.5,"Gilt Rate Yield Curve: https://www.bankofengland.co.uk/statistics/yield-curves > Latest Yield Curve Data > Use GLC Daily Data Current Month"]
[]
[null,"Real gilt rate",{"$f":"((1+C20/100)/(1+C17/100))-1","v":2.1248187094314419E-2}]
[]
["END"]

{"sheet":"Charts>>","index":8,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":true,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Charts"]

{"sheet":"Chart Format Instructions","index":9,"n_rows":29,"n_cols":1,"rows_kept":29,"cut":false,"hidden":true,"merged":[[28,0,29,17]]}
["Chart formatting tool"]
[]
["This toolkit allows you to automatically format your charts and graphs to the DfT statistics house style. "]
["Once formatted, the font on the charts and text will be a suitable size for reading and printing when pasted in word or used in presentations."]
[]
["To use the toolkit:"]
[]
["Go to the worksheet containing the chart to be formatted and click on it"]
["Select the appropriate chart type from the DfT tab at the top of the spreadsheet"]
["Click the appropriate button"]
[]
["The chart will be moved to a new sheet, if not already the case, and formatted to a consistent style. "]
["If you want to keep the original chart - take a copy before running the macro"]
["You can reposition the location of any text boxes or labels if you have applied if needed but do not adjust any formatting."]
[]
["Formatting the chart"]
[]
["When you run the macro for either bar or line charts you will be presented with some formatting options for your axes"]
["For bar charts, you will be asked to change the increments for your value axes labels. It is recommended you have no more than 6 values showing."]
["For example, if you have a chart with values ranging from 0 to 1000, you should have increments of 200"]
["For line charts, you will be asked the same as above and also the increments for your category axes. Try to make it so your labels do no overlap"]
["For example, if you have a chart showing data over time from 1990 to 2014, you may want to reduce the frequency of your labels to show one every 3 years"]
["For line charts, you will also be given two different label options to use for your chart. Where possible, move the text box labels and position them on the chart next to the lines and delete the legend above the chart"]
["Where this isn't possible, delete the individual text boxes and leave the legend above the chart"]
[]
["Exporting the chart"]
[]
["When copying and exporting the chart to another location e.g. word, the chart will look better if copied from it's own worksheet (i.e. how the macro places it)."]
["If you do embed it in a worksheet, the chart is set up to retain the original font size, even if the chart is resized. This is to ensure the font is still readable when copied and pasted elsewhere. Please do not adjust the font manaully - it will become small and unreadable when pasted elsewhere."]