Spreadsheet: 274695d1383193337-how-to-calculate-monthly-repayment-lab-exercise-assets-q-
Sheet: Sheet1

Acme Industries
Fixed Asset Register | Summary table
 | Type | Total Items | Total Cost |  |  | Asset type | Interest Rate | Term of Loan (years) | Term of Loan (months)
 | Buildings |  |  | SUMIFS FUNCTION |  | Buildings | 0.055 | 25
 | Vehicles |  |  |  |  | Vehicles | 0.13 | 4
 | Equipment |  |  |  |  | Equipment | 0.08 | 8
 | Tools |  |  |  |  | Tools | 0.06 | 5
Table #1: | Computers |  |  |  |  | Computers | 0.18 | 3
List of fixed assets with classes |  |  |  | (="Age is "&DATEDIF(C11,TODAY(),"y")&" Years, "&DATEDIF(C11,TODAY(),"ym")&" Months and "&DATEDIF(C11,TODAY(),"md")&" Days" )
Type | Description | Purchase Date | Cost | Age of Asset (month) | Monthly repayment
Buildings | Building #6 | 2010-08-01 00:00:00 | 419417.27 |  |  |  | if age of asset > vlookup term amount owing=0 else PMT(vlookup(Rate)/12, vlookup(Nper)*12,D11)
Tools | Tool #10 | 2010-07-24 00:00:00 | 82254.96
Equipment | Equipment #33 | 2010-07-04 00:00:00 | 6509.97
Equipment | Equipment #17 | 2011-01-20 00:00:00 | 97060.46
Equipment | Equipment #47 | 2011-01-20 00:00:00 | 36774.38
Tools | Tool #18 | 2010-12-18 00:00:00 | 20194.76
Vehicles | Car #17 | 2010-10-26 00:00:00 | 350795.36
Equipment | Equipment #7 | 2010-10-10 00:00:00 | 76190.98
Tools | Tool #6 | 2010-10-05 00:00:00 | 44871.24
Equipment | Equipment #10 | 2010-08-09 00:00:00 | 7070.14
Computers | Computer #19 | 2010-07-10 00:00:00 | 36773.35
Equipment | Equipment #42 | 2010-06-27 00:00:00 | 15775.78
Computers | Computer #14 | 2010-06-22 00:00:00 | 27553.42
Tools | Tool #13 | 2010-05-27 00:00:00 | 35603.35
Computers | Computer #15 | 2010-04-01 00:00:00 | 33623.56
Equipment | Equipment #9 | 2009-12-07 00:00:00 | 57017.25
Equipment | Equipment #45 | 2009-11-20 00:00:00 | 91793.96
Buildings | Building #3 | 2009-07-02 00:00:00 | 365584.99
Computers | Computer #5 | 2009-06-12 00:00:00 | 20601.05
Computers | Computer #20 | 2009-06-06 00:00:00 | 29779.68
Equipment | Equipment #30 | 2009-06-02 00:00:00 | 45384.39
Computers | Computer #18 | 2009-05-22 00:00:00 | 31640.03
Buildings | Building #4 | 2009-05-17 00:00:00 | 621465.76
Buildings | Building #14 | 2009-04-22 00:00:00 | 184073.6
Vehicles | Car #13 | 2009-04-09 00:00:00 | 347067.63
Computers | Computer #40 | 2009-01-25 00:00:00 | 32723.6
Computers | Computer #23 | 2009-01-19 00:00:00 | 25839.79
Vehicles | Car #12 | 2009-01-03 00:00:00 | 156883.53
Equipment | Equipment #39 | 2008-12-19 00:00:00 | 90886.05
Equipment | Equipment #50 | 2008-12-05 00:00:00 | 39863.08
Equipment | Equipment #43 | 2008-12-03 00:00:00 | 13440.04
Computers | Computer #41 | 2008-11-11 00:00:00 | 30981.79
Computers | Computer #13 | 2008-10-31 00:00:00 | 12750.12
Computers | Computer #30 | 2008-10-17 00:00:00 | 30132.06
Vehicles | Car #3 | 2008-10-05 00:00:00 | 486492.53
Vehicles | Car #7 | 2008-10-02 00:00:00 | 115202.41
Computers | Computer #7 | 2008-09-19 00:00:00 | 19154.65
Tools | Tool #3 | 2008-08-23 00:00:00 | 78809.16
Equipment | Equipment #21 | 2008-07-17 00:00:00 | 37966.36
Buildings | Building #2 | 2008-07-05 00:00:00 | 657421.04
Buildings | Building #18 | 2008-06-01 00:00:00 | 760660.43
Computers | Computer #3 | 2008-05-25 00:00:00 | 44620.58
Tools | Tool #23 | 2008-04-18 00:00:00 | 13652.92
Vehicles | Car #10 | 2008-02-26 00:00:00 | 275462.99
Tools | Tool #17 | 2008-02-21 00:00:00 | 53821.74
Equipment | Equipment #1 | 2007-12-21 00:00:00 | 68828.84
Computers | Computer #39 | 2007-11-02 00:00:00 | 39073.62
Tools | Tool #12 | 2007-10-26 00:00:00 | 81620.15
Computers | Computer #25 | 2007-10-14 00:00:00 | 40440.46
Computers | Computer #36 | 2007-09-21 00:00:00 | 45938.53
Tools | Tool #32 | 2007-09-06 00:00:00 | 95398.22
Computers | Computer #9 | 2007-07-29 00:00:00 | 47719.21
Computers | Computer #11 | 2007-07-27 00:00:00 | 44002.59
Equipment | Equipment #41 | 2007-07-06 00:00:00 | 45096.04
Equipment | Equipment #37 | 2007-05-17 00:00:00 | 30755.45
Vehicles | Car #4 | 2007-05-14 00:00:00 | 160969.44
Equipment | Equipment #18 | 2007-04-11 00:00:00 | 65978.61
Equipment | Equipment #2 | 2007-04-10 00:00:00 | 16754.68
Tools | Tool #26 | 2007-03-27 00:00:00 | 19465.36
Computers | Computer #35 | 2007-03-19 00:00:00 | 22835.65
Vehicles | Car #14 | 2007-02-23 00:00:00 | 299383.1
Vehicles | Car #15 | 2007-02-14 00:00:00 | 45279.88
Computers | Computer #37 | 2007-01-24 00:00:00 | 49647.22
Vehicles | Car #11 | 2007-01-08 00:00:00 | 358789.31
Equipment | Equipment #13 | 2006-11-06 00:00:00 | 80974
Equipment | Equipment #36 | 2006-10-25 00:00:00 | 70446.98
Equipment | Equipment #25 | 2006-10-12 00:00:00 | 69758.32
Tools | Tool #9 | 2006-08-04 00:00:00 | 47141.95
Tools | Tool #1 | 2006-07-27 00:00:00 | 21293.02
Equipment | Equipment #40 | 2006-06-05 00:00:00 | 9344.24
Equipment | Equipment #29 | 2006-05-12 00:00:00 | 41181.37
Tools | Tool #16 | 2006-04-11 00:00:00 | 24424.3
Computers | Computer #29 | 2006-03-19 00:00:00 | 37528.07
Equipment | Equipment #22 | 2006-03-19 00:00:00 | 12281.52
Tools | Tool #27 | 2006-02-26 00:00:00 | 12524.01
Equipment | Equipment #44 | 2006-02-10 00:00:00 | 29618.4
Buildings | Building #12 | 2006-02-07 00:00:00 | 75344.29
Vehicles | Car #6 | 2006-01-06 00:00:00 | 8548.63
Equipment | Equipment #19 | 2005-12-30 00:00:00 | 28413.08
Equipment | Equipment #3 | 2005-10-07 00:00:00 | 89084.17
Equipment | Equipment #23 | 2005-10-05 00:00:00 | 6312.1
Tools | Tool #25 | 2005-09-17 00:00:00 | 90454.3
Computers | Computer #2 | 2005-08-03 00:00:00 | 29315.82
Equipment | Equipment #34 | 2005-08-02 00:00:00 | 7619.63
Computers | Computer #22 | 2005-06-17 00:00:00 | 33118.7
Equipment | Equipment #24 | 2005-05-16 00:00:00 | 17623.99
Computers | Computer #17 | 2005-05-10 00:00:00 | 47318.61
Equipment | Equipment #20 | 2005-04-29 00:00:00 | 88800.25
Equipment | Equipment #16 | 2005-04-26 00:00:00 | 51949.24
Computers | Computer #12 | 2005-04-25 00:00:00 | 29501.42
Tools | Tool #8 | 2005-04-23 00:00:00 | 12222.67
Buildings | Building #9 | 2005-04-14 00:00:00 | 417744.24
Computers | Computer #33 | 2005-03-22 00:00:00 | 14521.13
Tools | Tool #22 | 2005-03-21 00:00:00 | 46494.39
Equipment | Equipment #32 | 2005-03-04 00:00:00 | 55509.54
Computers | Computer #24 | 2005-02-09 00:00:00 | 26287.07
Tools | Tool #4 | 2004-12-28 00:00:00 | 3997.32
Tools | Tool #34 | 2004-12-20 00:00:00 | 97496.91
Buildings | Building #17 | 2004-12-19 00:00:00 | 460192.38
Tools | Tool #33 | 2004-10-11 00:00:00 | 38333.6
Vehicles | Car #18 | 2004-09-25 00:00:00 | 426967.23
Computers | Computer #28 | 2004-09-22 00:00:00 | 23781.53
Computers | Computer #8 | 2004-09-13 00:00:00 | 10910.15
Tools | Tool #7 | 2004-09-05 00:00:00 | 49946.41
Vehicles | Car #2 | 2004-08-05 00:00:00 | 448821.88
Buildings | Building #8 | 2004-08-05 00:00:00 | 363731.43
Tools | Tool #2 | 2004-07-20 00:00:00 | 5875.18
Tools | Tool #24 | 2004-07-13 00:00:00 | 72853.33
Vehicles | Car #16 | 2004-06-29 00:00:00 | 266870.95
Computers | Computer #45 | 2004-04-15 00:00:00 | 42629.16
Computers | Computer #34 | 2004-04-10 00:00:00 | 29462.78
Equipment | Equipment #49 | 2004-04-07 00:00:00 | 16446.52
Tools | Tool #29 | 2004-03-27 00:00:00 | 98730.34
Equipment | Equipment #4 | 2004-03-24 00:00:00 | 8346.19
Computers | Computer #6 | 2004-02-23 00:00:00 | 18369.82
Tools | Tool #20 | 2004-02-22 00:00:00 | 39610.46
Equipment | Equipment #8 | 2004-01-22 00:00:00 | 87593.43
Tools | Tool #28 | 2004-01-03 00:00:00 | 51806.48
Buildings | Building #15 | 2003-12-21 00:00:00 | 998182.55
Computers | Computer #43 | 2003-12-21 00:00:00 | 31945.09
Vehicles | Car #20 | 2003-12-07 00:00:00 | 259798.24
Equipment | Equipment #15 | 2003-11-17 00:00:00 | 14652.84
Buildings | Building #11 | 2003-11-11 00:00:00 | 970763.06
Tools | Tool #19 | 2003-11-01 00:00:00 | 35413.81
Equipment | Equipment #14 | 2003-10-16 00:00:00 | 26663.26
Computers | Computer #32 | 2003-09-01 00:00:00 | 22588.67
Vehicles | Car #1 | 2003-08-19 00:00:00 | 485345.4
Tools | Tool #5 | 2003-07-24 00:00:00 | 11545.96
Computers | Computer #16 | 2003-07-22 00:00:00 | 26593.33
Computers | Computer #26 | 2003-07-15 00:00:00 | 37519.59
Computers | Computer #1 | 2003-06-21 00:00:00 | 21188.08
Equipment | Equipment #6 | 2003-04-27 00:00:00 | 92779.73
Buildings | Building #1 | 2003-04-18 00:00:00 | 23982
Tools | Tool #14 | 2003-03-23 00:00:00 | 81714.13
Computers | Computer #21 | 2003-03-16 00:00:00 | 19361.49
Tools | Tool #30 | 2003-01-28 00:00:00 | 49554.79
Tools | Tool #15 | 2002-11-29 00:00:00 | 72218.77
Computers | Computer #44 | 2002-11-28 00:00:00 | 21517.3
Vehicles | Car #23 | 2002-11-26 00:00:00 | 493747.25
Equipment | Equipment #11 | 2002-11-18 00:00:00 | 71956.62
Equipment | Equipment #12 | 2002-11-11 00:00:00 | 66739.72
Computers | Computer #38 | 2002-10-22 00:00:00 | 12544.45
Equipment | Equipment #48 | 2002-10-10 00:00:00 | 42420.9
Equipment | Equipment #26 | 2002-08-14 00:00:00 | 6562.33
Vehicles | Car #19 | 2002-07-29 00:00:00 | 84138.2
Buildings | Building #5 | 2002-07-04 00:00:00 | 626535.8
Computers | Computer #31 | 2002-06-23 00:00:00 | 53922.16
Equipment | Equipment #35 | 2002-04-22 00:00:00 | 80936.72
Computers | Computer #4 | 2002-04-07 00:00:00 | 33513.29
Buildings | Building #16 | 2002-02-06 00:00:00 | 362106.59
Tools | Tool #21 | 2002-02-03 00:00:00 | 32537.26
Tools | Tool #31 | 2002-02-02 00:00:00 | 27057.17
Buildings | Building #7 | 2002-02-01 00:00:00 | 308334.43
Vehicles | Car #24 | 2002-01-24 00:00:00 | 445971.7
Vehicles | Car #8 | 2002-01-10 00:00:00 | 140668.93
Equipment | Equipment #27 | 2001-12-31 00:00:00 | 96281.99
Equipment | Equipment #5 | 2001-11-26 00:00:00 | 10852.99
Computers | Computer #10 | 2001-10-23 00:00:00 | 19962.04
Computers | Computer #27 | 2001-09-29 00:00:00 | 18977.39
Vehicles | Car #21 | 2001-09-05 00:00:00 | 191849.52
Equipment | Equipment #38 | 2001-07-23 00:00:00 | 12983.36
Equipment | Equipment #28 | 2001-06-21 00:00:00 | 63392.24
Computers | Computer #42 | 2001-06-11 00:00:00 | 43332.7
Vehicles | Car #9 | 2001-05-26 00:00:00 | 427658.59
Tools | Tool #11 | 2001-04-14 00:00:00 | 12611.86
Buildings | Building #13 | 2001-02-17 00:00:00 | 167597.27
Buildings | Building #10 | 2001-01-13 00:00:00 | 877482.89
Equipment | Equipment #46 | 2001-01-12 00:00:00 | 82290.06
Vehicles | Car #5 | 2000-09-25 00:00:00 | 61761.05
Vehicles | Car #22 | 2000-07-05 00:00:00 | 197391.93
Equipment | Equipment #31 | 2000-05-23 00:00:00 | 80320.16