ࡱ> O  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNRoot Entry F  1i ^Workbook oOle ObjInfo Oh+'0HP`p DETRfSOLIVERMicrosoft Excel@M@M7C@՜.+,0 PXh px OlePres000,#SummaryInformation(DocumentSummaryInformation8HCompObj fM6" y B   ''  ' --   --'-- --  $``MM`---'-- M`l` M`M`M`qMq`M`TMT`M--'-- l-` `MM`--'-- -M MArial xxx  -UMUMUMUqMqUMUTMTUM--"System  -'---  ---'--- S]---'--- P]--' p>p>yUyUkk  !!88NNee||11HH__vv-'>>UUkk  !!88NNee||11HH__MvMvMMMMM-'>>UUkk  !!88NNee||11HH__MvMvMMMMM3f-'p>p>yUyUkk  !!88NNee||11HH__MvMvMMMMM3-'p>p>yUyUkk  !!88NNee||11HH__MvMvMMMMM'p>yUk !8Ne|1H_v---'--- S]---'--- ArialY xxxY ------'--- B\  B2 `'Chart 5: Switching to an annuity system      ----'--- ---'--- ----'---   2 f!1 2 f6 2 f11 2 fo16 2 f21 2 fR26 ---'--- Arial xxx  ------'--- M 2 QYears ----'--- Arial -xxx - - - -  l---'- --  m---'- --  m-  t  2 Current system   ---'- --  m---'- --  m-  } "2 Annuity from start    ---'- --  m---'- --  m-  t %2 Annuity after 1 year     ---'- --  m---'- --  m3f-  } '2 Annuity after 3 years      ---'- --  m---'- --  m3-  t '2 Annuity after 5 years      ---'- --  m---'- --  ---'- --  -  -   -- '  '  ' u'ɀ\pNHARRISOrewsa Ba= =j,u!8X@"1Arial1Arial1Arial1Arial1Arial1Arial1Tahoma1Arial1 Arial1Arial""#,##0;\-""#,##0""#,##0;[Red]\-""#,##0""#,##0.00;\-""#,##0.00#""#,##0.00;[Red]\-""#,##0.005*0_-""* #,##0_-;\-""* #,##0_-;_-""* "-"_-;_-@_-,)'_-* #,##0_-;\-* #,##0_-;_-* "-"_-;_-@_-=,8_-""* #,##0.00_-;\-""* #,##0.00_-;_-""* "-"??_-;_-@_-4+/_-* #,##0.00_-;\-* #,##0.00_-;_-* "-"??_-;_-@_- 0.000 ""#,##0% ""#,##0.000;[Red]\-""#,##0.000 ""#,##0.00 ""#,##0.000 0.000000% 0.0% yyyym/d0.0 ""#,##0.0 #,##0.000000 #,##0.000 00000                + ) , *  #   $     @    #0@ #0 #0   $"@  $" @  $""@ @  ""@ @  d #|  (""@ @ (0@@  d@ @   ("@@  d@ @  @     d""@ @ /      "@ @ #|!"@ @  #|!"@ @ (  "@@  `!"@ @ . (0"@ @  `!"@ @ *  d!"@ @ 5  (@@  d@  @  d@  d @   (@ @   (@  ( 1  (0@ @  d@ @   $ #0@@ #0@ #0 @  @   @  8@   *0@@ *0  @ `A Commentary .(Data6Annuity,  ;? TH 3  @@  SPGrantYearAprilMayJuneJulyAugust SeptemberOctoberNovemberDecemberJanuaryFebruaryMarchStarting Month Interest rateTOTALCumulative grantANNUITYAnnual annuityContract end monthLookup number 1Lookup number 2Annuity period startAnnuity period endLength of annuity paymentsYEAR 1 PFI creditsScaling FactorLocal AuthorityProjectHOW DOES THIS SPREADSHEET WORK?INPUTS REQUIRED1 April 2002, or (for certain schools schemes) where the PFI credits calculation used the old discounting method, ie using the |signature. For projects which were endorsed, but which had not reached contract signature, before 1 April 2005 the rate will1. PFI credits: This is the level of credits issued by the sponsoring department. In certain cases - where the project was endorsed before 4. Interest rate: For all projects which reached contract signature before 1 April 2005, the rate will be that in force in the year of contractContract end year First payment yearThe period is found by deducting the start date from the end date. The two dates are found by using a lookup table to convert the LOOKUP TABLEYExample: Start April 2006, end Jan 2026 = 2026.042 - 2006.292 = 19.750 (ie 19 yrs 9 mths)month to a fraction of 1 (column E of the lookup table) and adding that to the year. Thus April 2006 becomes 2006 + 0.292 = 2006.292.table. For the last year the annual annuity figure is multiplied by the end month factor (the inverse of the old Factor X), found by deducting=the figure in the same column D of the lookup table from 1.0.Start month factorEnd month factorFor the first year the annual annuity figure is multiplied by the start month factor (the old Factor X), found in the column D of the lookupExample: Start April 2006, start month factor = 0.958 (ie 11.5 mths); end Jan 2026, end month factor = 0.792 (ie 9.5 mths). Total payments lare 11.5 mths in year 1, 18 years receiving full annuity, 9.5 mths in year 20 = 19 yrs 9 mths total payment.be 6.3%. For projects endorsed after 1 April 2005, the rate will be that in force at the date of endorsement. For 2005/06 this is 6.3%.CHECKFirst financial yearLast financial yearis Apr - Dec, in which case it uses the year as the financial year. If it is Jan - Mar it shows the financial year as the year before, ie}Jan 2026 is shown as financial year 2025 (that is 2025/06). The year in column C is then compared to the last financial year.Financial YearKtrigger grant. The year must show the actual year, not the financial year. 3. End of contract: The month in which contract expiry occurs. The dates are entered in the same way as those for the start of grant payment.2. Start of grant payment: The year and month to be entered will be the month in which full service commencement occurs, i.e. when the main asset (or first phase of a phased project) becomes operational. Where the project involves maintenance of transferred assets it will be for the sponsoring department to decide whether significant investment has started. Interim services and temporary assets will not6. The spreadsheet calculates the annual grant by doing an annuity calculation using the PMT function: PMT (interest rate, period, -PFI credits).m8. To produce the final annuity, the figure produced by the PMT function is multiplied by the scaling factor.9. The last part of the annuity calculations is to pro-rata the payments in the first and last years according to the start or end month.10. In order to determine the last year of payments the spreadsheet calculates the last financial year by using the lookup table to see whether the month(ii) the length of the annuity is based on using mid-month points for the start and end dates rather than being accurate to the day. This is bconsistent with the way in which the level of grant has always been calculated for the first year.(i) the interest in the annuity is calculated annually, using the opening balance to calculate interest for the whole year, even though payments are quarterly.7. Various options are possible for the annuity calculation. For PFI annuity grant the following simplifying assumptions have been adopted: see commentary para 1see commentary para 2see commentary para 3see commentary para 4see commentary para 5 CALCULATIONScontract signature date as the base - 'uplift' should be applied to the figure. The method is described in the Grant Determination.4For later years, see the latest Grant Determination.5. Scaling factor: For all projects endorsed in 2005/06 or earlier the rate will be 1.0. For later years, see the latest Grant Determination.RO L s0 h%0  + u'ɀ 3"%'  dMbP?_*+%& BP(?' BP(?(M&d2?)M&d2?MNHP LaserJet 5Mps XXA4ter % .''''" PXX `? `?U} } I 3        # ! M ; < = 9 : $ " 2 N O L > ' * ) F D2   !"$&'()+,-/02 E !B "C $? &@ '/ (+ ), +A ,6 -7 /0 012 >@7 u'ɀ  <*3  dMbP?_*+%MNHP LaserJet 5Mps XXA4ter % .''''" dXX??U} m} I} I } I} I } I} m } $ } }  } $ Column IX  w@ @ @ @  @  @  @    @    @  ,@  @    @  @ @ @ w@   // / 68 JGK$ 6&~ 7T@ JHK ). |Z 17 JHK ).Z  6%~ 5@ JI K  4 5 JI K 13T㥛 ? JJK % 1~ 2? JKK0 9(+++ > ~ ?+-?㥛 ?~ :$@ @?? ;  ~ @㥛 ?-?1:?D%%B  @?? ;-  ~ @9v?/$?1<?D%%B ~ @-?MbX? ? ~ @MbX?Zd;O?~ :@  ~  @/$?9v? ; "@??1:/$?D%%B  $@?? ;. ~ &@Zd;O?MbX?6=MbX? D%%B ~ (@,MbX?-? Dl 0 ci4 084 N8N_NuNN8_Nz0   %  (  ^  c $@\]f g P|  HR$ HR%  f^  c $@ \ ]f dg |  R$ R%  >@:  7 u'ɀ 48{Yn  dMbP?_*+%MNHP LaserJet 5Mps XXA4ter % .''''" dX??U} m} } $} } I} } m} } 4  @ h@ X@ v@ v@ v@   @                           '7*n*8@!Z ZZZ A '7(!ZDZB;Z- B4JCT@ 4Z? Z:B- B5JC@ 4Z? Z : B- # L8M # #~ H?$E{ D 0-A02005-&{D A02006#  DZ D A DT@ D H@    L F{   /'L 0-A02006- {   /'LA02007b  LD D  $3D D DZ "B     /' LL  X@  D  - H@   F{  2007-  {  2008b  LD D  $3D D DZ "B      \@   / & L - H@   F{  2008- ${  2009b  LD D  $3D D DZ "B      `@   H@   F0{  2009- <{  2010b  LD D  $3D D DZ "B      d@   H@  FH{  2010- T{  2011b  LD D  $3D D DZ "B     h@  H@ F`{ 2011-l{  2012b LD D  $3D D DZ "B   l@ H @/! LFx{ 2012-{  2013b LD D  $3D D DZ "B   p@ H"@F{ 2013-{  2014b LD D  $3D D DZ "B   t@ H$@F{ 2014-{  2015b LD D  $3D D DZ "B   x@ H&@F{ 2015-{  2016b LD D  $3D D DZ "B   |@ H(@F{ 2016-{  2017b LD D  $3D D DZ "B   @ H*@F{ 2017-{  2018b LD D  $3D D DZ "B   @ H,@F{ 2018-{  2019b LD D  $3D D DZ "B   @ H.@F { 2019-,{  2020b LD D  $3D D DZ "B   @ H0@F8{ 2020-D{  2021b LD D  $3D D DZ "B   @ H1@FP{ 2021-\{  2022b LD D  $3D D DZ "B   @ H2@Fh{ 2022-t{  2023b LD D  $3D D DZ "B   @ H3@F{ 2023-{  2024b LD D  $3D D DZ "B   @ H4@F{ 2024-{  2025b LD D  $3D D DZ "B   @ H5@F{ 2025-{  2026b LD D  $3D D DZ "B   @ H6@F{ 2026-{  2027b LD D  $3D D DZ "B   @ H7@F{ 2027-{  2028b LD D  $3D D DZ "B   @ H8@ F{ 2028-{  2029b LD D  $3D D DZ "B    @ DlSSffLC,"""9"""""""""""""""  ! @" # $ % & ' ( ) * + , - . /  0 1 2 3  H9@! F{  2029- {!  2030b  LD D  $3D D DZ "B  !   @  !H:@"!F({! 2030-!4{"  2031b! !LD! D  $3D! D DZ "B! " !! @! "H;@#"F@{" 2031-"L{#  2032b" "LD" D  $3D" D DZ "B" # "" @" #H<@$#FX{# 2032-#d{$  2033b# #LD# D  $3D# D DZ "B# $ ## @# $H=@%$Fp{$ 2033-$|{%  2034b$ $LD$ D  $3D$ D DZ "B$ % $$ ğ@$ %H>@&%F{% 2034-%{&  2035b% %LD% D  $3D% D DZ "B% & %% ȟ@% &H?@'&F{& 2035-&{'  2036b& &LD& D  $3D& D DZ "B& ' && ̟@& 'H@@('F{' 2036-'{(  2037b' 'LD' D  $3D' D DZ "B' ( '' П@' (H@@)(F{( 2037-({)  2038b( (LD( D  $3D( D DZ "B( ) (( ԟ@( )HA@*)F{) 2038-){*  2039b) )LD) D  $3D) D DZ "B) * )) ؟@) *HA@+*F{* 2039-* {+  2040b* *LD* D  $3D* D DZ "B* + ** ܟ@* +HB@,+F{+ 2040-+${,  2041b+ +LD+ D  $3D+ D DZ "B+ , ++ @+ ,HB@-,F0{, 2041-,<{-  2042b, ,LD, D  $3D, D DZ "B, - ,, @, -HC@.-FH{- 2042--T{.  2043b- -LD- D  $3D- D DZ "B- . -- @- .HC@/.F`{. 2043-.l{/  2044b. .LD. D  $3D. D DZ "B. / .. @. /ID@/Gx{/ 2044-/{ 2045b/!/LD/ D  $3D/ D DZ "B/!3 // @/ 0 1& 1@@#1"4| %0 3&3 3@@!3"1 DD,@|"""""""""""""""" E<   / 1 PH  0(   >@ 7 DETRa  CommentaryDataAnnuityCommentary!Print_Area  Worksheets Named Ranges FMicrosoft Excel WorksheetBiff8Excel.Sheet.89q