Calculating Interest in Excel by rav16913

VIEWS: 10 PAGES: 2

More Info
									This table approximates the breakeven purchase price for calves and yearlings at different corn and selling prices given
a set of assumptions about cattle performance and other input cost. Enter the information in column B that represents your
feeding situation for the type of cattle you plan to feed.




Feeder Cattle Break Even Purchase Price for a Steer Calf
                                      Corn                            Fed Cattle Selling Price
                                      Price       $68.00           $70.00    $72.00       $74.00        $76.00
In weight                    550          $1.80        86.51        90.48       94.44       98.41       102.37
Out weight                  1150          $2.00        84.28        88.25       92.21       96.17       100.14
Target ADG                  2.85          $2.20        82.05        86.01       89.98       93.94       97.91
Death loss                1.50%           $2.40        79.82        83.78       87.75       91.71       95.68
Corn (bu)                   63.0          $2.60        77.59        81.55       85.52       89.48       93.44
Hay (ton)                    0.5          $2.80        75.36        79.32       83.28       87.25       91.21
Hay Price ($/t)         $100.00           $3.00        73.12        77.09       81.05       85.02       88.98
Supplement ($/hd)        $23.00           $3.20        70.89        74.86       78.82       82.79       86.75
Interest                  9.50%           $3.40        68.66        72.63       76.59       80.55       84.52
Yardage                   $0.25           $3.60        66.43        70.39       74.36       78.32       82.29
Vet-Med                  $14.00           $3.80        64.20        68.16       72.13       76.09       80.06
Trucking                  $9.00           $4.00        61.97        65.93       69.90       73.86       77.82
Other                     $5.00           $4.20        59.74        63.70       67.66       71.63       75.59
Target Return             $0.00           $4.40        57.50        61.47       65.43       69.40       73.36


Feeder Cattle Break Even Purchase Price for a Yearling Steer
                                      Corn                      Fed Cattle Selling Price
                                      Price      $68.00      $70.00    $72.00       $74.00              $76.00

In weight                    750          $1.80        79.45        82.66       85.86       89.06        92.27
Out weight                  1250          $2.00        77.81        81.01       84.21       87.42        90.62
Target ADG                   3.2          $2.20        76.16        79.36       82.56       85.77        88.97
Death loss                0.75%           $2.40        74.51        77.71       80.92       84.12        87.32
Corn (bu)                   63.0          $2.60        72.86        76.07       79.27       82.47        85.68
Hay (ton)                   0.35          $2.80        71.22        74.42       77.62       80.83        84.03
Hay Price ($/t)         $100.00           $3.00        69.57        72.77       75.98       79.18        82.38
Supplement ($/hd)        $10.00           $3.20        67.92        71.13       74.33       77.53        80.74
Interest                  9.50%           $3.40        66.28        69.48       72.68       75.88        79.09
Yardage                   $0.25           $3.60        64.63        67.83       71.03       74.24        77.44
Vet-Med                   $8.00           $3.80        62.98        66.18       69.39       72.59        75.79
Trucking                 $11.00           $4.00        61.33        64.54       67.74       70.94        74.15
Other                     $5.00           $4.20        59.69        62.89       66.09       69.30        72.50
Target Return             $0.00           $4.40        58.04        61.24       64.45       67.65        70.85

Change in Feeder Cattle Bid Price for a Given Change in Selected Variables
                                                      Calf Yearling
Profit                          $10                  $1.77       $1.31
Average Daily Gain              ###                  $1.38       $0.81
Feed Efficiency @ $2.60 corn    ###                  $3.43       $2.42
Death Loss                      ###                  $0.10       $0.05
Feeder Cattle Budget Worksheet (Central Iowa)
Prepared by Iowa State University Extension Economics (515-294-6290)

             Date:                          23-Oct-97
                                    BackGround        Steer      Steer     Heifer
Purchase Weight                           500#        550#       750#       700#
Finished Weight                           775#       1150#      1250#      1050#
Days on Feed                               125         210        156        136
Projected Marketing Date             25-Feb-98  21-May-98   28-Mar-98  08-Mar-98
====================== =========== ========== ========== ========== ==========
COSTS (per head):
Feeder Price/cwt                        $90.00      $87.50     $80.00     $77.00
 Feeder Cost                           $450.00     $481.25    $600.00    $539.00

FEED COSTS:
Corn Price per bu.                               $2.50
Corn Bu. Fed                                                         26.5              63.0               63.0               47.0
 Corn Cost                                                         $66.25           $157.50            $157.50            $117.50

Alfalfa-Brome Hay Price $/ton                 $100.00
Hay fed (as fed tons)                                                0.30               0.50               0.35               0.25
 Hay Cost                                                          $30.00             $50.00             $35.00             $25.00

Supplement Price/lb.                                                $0.15              $0.13              $0.08              $0.08
Supplement Used - in lbs.                                             125                181                117                102
 Supplement Cost                                                   $18.75             $23.53              $9.36              $8.16
TOTAL FEED COSTS:                                                $115.00            $231.03            $201.86            $150.66
OTHER COSTS:
Vet medical & operating costs                                      $10.00             $18.70             $13.80             $12.75
Interest - Feeder                                9.50%             $14.64             $26.30             $24.36             $19.08
      - Feed/Operating                           9.50%              $2.03              $6.82              $4.38              $2.89
Labor Cost per hour                              $7.00              $9.00             $21.00             $14.00             $10.50
Death Loss % of purchase price                                      1.50%              1.50%              0.75%              0.75%
      - Cost                                                        $6.75              $7.22              $4.50              $4.04
Transportation/Marketing Cost                                       $6.50              $8.80             $10.80              $9.80
TOTAL OTHER COSTS                       $48.92     $88.85     $71.84     $59.06
====================== =========== ========== ========== ========== ==========
TOTAL VARIABLE COSTS (per head)        $613.92    $801.13    $873.70    $748.72
TOTAL FIXED FACILITY COSTS              $11.30     $18.90     $14.20     $12.30

TOTAL COST TO FINISH ANIMAL            $625.22    $820.03    $887.90    $761.02
====================== =========== ========== ========== ========== ==========
Necessary Selling Price/cwt:
1.) To Cover Variable Costs             $79.22     $69.66     $69.90     $71.31
2.) To Cover Total Costs                                           $80.67             $71.31             $71.03             $72.48
Futures Price as of                         23-Oct-97              $78.75             $70.42             $72.57             $72.57
Estimated Basis                                                     ($1.00)             $1.31             ($0.48)            ($1.21)
-------------------------------------- ------------------- ------------------ ------------------ ------------------ ------------------
Expected Price/cwt by Hedging                                      $77.75             $71.73             $72.09             $71.36
Est. Return over Var. Costs/head                                  ($11.36)            $23.77             $27.43               $0.56
Est. Return over Total Costs/head                                 ($22.66)              $4.87            $13.23            ($11.74)
Value of corn marketed through cattle after all cost                 $1.64              $2.58              $2.71              $2.25

								
To top