Download the original spreadsheet file:

  • You have been given responsibility for a portfolio of ten energy enterprises.




  • The names of the energy enterprises are One, Two, Three and so on … you get the idea.




  • Your responsibilities are financial, social and environmental.




  • For each enterprise you know: term (# of years to be repaid), interest rate, the amount being invested.




  • All the investments will be made in Year Zero




  • You should open a new file in a spreadsheet program such as Excel and begin with the following information




Name
Term
(yrs)

Interest
Rate

Amount
Other Information
One
7
10.7%
150000
Equal Annual Payment Method (PMT)
Two
5
8.0%
200000
Int Only Yrs 1-4, P+I in Year 5, Plus Carbon Monetized
Three
5
9.0%
100000
Equal PMT Method
Four
3
10.0%
750000
Int Only Yrs 1 &2, P+I in Year 3
Five
9
10.7%
225000
Equal PMT Method
Six
9
4.0%
175000
Int Only Yrs 3-8 Plus 2X Principal, and Carbon Monetized
Seven
6
12.0%
100000
Equal PMT Method
Eight
7
10.7%
220000
Equal PMT Method Plus Carbon Monetized
Nine
5
10.7%
120000
Equal PMT Method
Ten
8
11.0%
120000
Equal PMT Method



2160000

Assignment 1: set up work paper based on these four inputs and calculate the scheduled principal and interest payments from these ten investments. All investments are made in Year Zero and repaid in Years 1-9




Assignment 2: calculate the portfolio rate of return.




Assignment 3: Using the following estimates, deduct from this portfolio the cost incurred by you and your colleagues to invest and harvest these investments.




Year 0
Cost



1
250000



2
100000



3
100000



4
100000



5
25000



6
25000



7
25000



8
25000



9
1250000



Assignment 4: Calculate the new portfolio cash flow by year and determine the rate of return after costs.




Assignment 5: Insert the following data as to number of people permanently served and tonnes of carbon dioxide equivalent saved per year per enterprises
People
CO2e/year


Enterprise One

17000
7600


Two
34000
33000


Three
17000
10000


Four
2500
1000


Five
9000
12000


Six
60000
45000


Seven
18500
28500


Eight
25000
20000


Nine
25000
2300


Ten
25000
20000


Assignment 6: Calculate the Investment Required per person served and per tonne per year. Calculate this based on just the amount of the loans as well as across the portfolio after deducting the costs for you and your colleagues.




Assignment 7: Monetize the carbon for the few enterprises so noted. Assume that in order to monetize the carbon you must spend an additional $6 per tonne in Year 1 but that you will receive $6 per tonne in each of Year 2 through 6.




Assignment 8: What is the "all-in return" on your portfolio after taking into account carbon monetization revenues.




Assignment 9: Based on what you now know what is the reasonable interest rate you should promise on the portfolio to lenders and investors with or without carbon included?