Weekly Nugget: Analyzing financial situations
Benchmarking public sector infrastructure projects.
Equivalent Cash Flows menu item
May 22nd, 2025
Introduction
Public sector projects vary in complexity and size, but it is always possible to analyze them from a financial perspective. Even though public sector companies are non for profit, it is important that they are able to operate in a self-sufficient manner. Furthermore, analyzing investments and expenditures in public sector projects serves to discourage harmful corruption practices.
One of the most important services that a government may provide is electric power supply. Electricity cannot be stored, at least not in any significant way, and so control centers must carefully monitor demand to activate and shut down generation plants as needed. According to the International Energy Agency, global demand for electricity is expected to grow at a rate of 3.4% per year over the next three years.
Here, we will analyze the construction and operation of enough hydroelectric plants to meet the demand for additional electrical energy in Mexico. Installed electric energy capacity in Mexico is currently around 71,120 MW and if its demand grows according to the global mean, it will need to increase its installed capacity by approximately 2,400 MW per year.
Situation to be analyzed
Hydroelectric plants require several years of planning to prospect plausible locations where a dam could be built. Dams certainly constitute beautiful civil engineering projects, and although all human made constructions impact the environment, hydroelectric power plants use a renewable energy source and once in operation emit very low greenhouse gases. In addition, they can remain operational, if properly maintained, for well over half a century.
According to Statista, the global average hydropower installation costs per Megawatt were 2.881 million USD in 2022. That means, an average investment of 6.914 billion USD per year using this type of electric power generation.
One thing is the capacity to supply electrical energy and another is its consumption. That is, when we use electricity to do things: light houses, operate appliances at home and operate machinery in factories. That is power: the amount of energy transferred or converted per unit of time. In order to consume the electrical energy, it must be delivered to your home, and so we will assume a 10% additional investment to improve the power grid necessities as new generation plants are incorporated to it.
Total consumption of electricity or electrical power used in 2023 in Mexico was 356,416 Giga Watt hour (GWh), which is equivalent to operating the full installed generation capacity at 100% for 208 days. That means, every Megawatt of continuous supply could result in close to 5-Gigawatt hour per year of energy consumed or electrical power used. So, the 2,400 MW of additional installed capacity would translate into 12,000 GWh consumed per year.
Financial Assumptions
We will assume the following: yearly inflation rate in price of electricity 2%, interest rates will be in USD and set for a “BBB” rating (consistent with Mexico's current international credit rating). Net Benefit as a percentage of revenues will be set at 20%.
We will assume that investments, totaling 7.6 Billion USD will occur according to the following table:
| Year |
Amount
B USD |
Year |
Amount
B USD |
| 1 |
1.5 |
2 |
1 |
| 3 |
1 |
4 |
1 |
| 5 |
1 |
6 |
0.6 |
| 7 |
0.5 |
|
|
We need to find the price P0 per GWh that would result in a feasible operation.
Starting in year 8 the net benefit will be computed as follows:
P0 * 12000GWh/year * .2
After trying several prices, we found that setting P0 equal to 0.00022 billion USD or 0.22 USD per KWh results in a Net Present Value equal to just 60 million USD. See figure 1 for the project's net free cash flows, and figure 2 for its NPV obtained using finnugget's equivalent cash flow menu item.
We used an exponential perpetuity rate of 2% keeping inflation throughout the project set at that level.
Results
The 0.22 USD/KWh in 8 years from now is equivalent to 0.1805 in 2023 According to Statista the most expensive country at that time was Ireland with a price of 0.53 USD/KWh, Belgium 0.43, Germany 0.4, Denmark 0.35, France and Australia 0.28, Uruguay 0.26, Peru 0.19, the US 0.17 and Mexico 0.12
Conclusion
Clearly understanding the problem and establishing all the major relations and assumptions are the key elements in any evaluation. The financial analysis is the easy component. In any public sector project cost inflation is the source of corruption and therefore should be the focus of any analyst's attention. That is why benchmarking investments is important. Finnugget may assist you in determining whether a proposed investment makes sense or if it will turn out to be a future costly endeavor.
Governments don't go bankrupt. They merely increase their debts and that burden will be on us, that is why it is important to monitor their decisions and to participate actively as responsible citizens wherever you may live.
Let us know what you think. Until the next post!
Previous Posts:
- May 14th, 2025:
Analyzing investments in foreign risk free bonds.
- May 7th, 2025:
Estimating the loss in intrinsic value as a result of Tesla's revenue slowdown.
- April 22nd, 2025:
Using Swaps to reduce the uncertainty of future costs.
- April 11th, 2025:
Arbitrage opportunities with Forward contracts on Gold.
- April 2nd, 2025:
Trading strategies: Bear Spread with Tesla Options.
- March 26th, 2025:
Valuing Options, an example with a share of Apple.
- March 19th, 2025:
Investment Analysis in Stock Markets
- March 12th, 2025:
Cash Flow Transformations in Debt Management
- March 4th, 2025:
Term structures of interest rates in financial planning
- Feb 24,2025:
How Good is an investment in government bonds in the US and Mexico today?
Analyzing investments in foreign risk free bonds.
Upcoming Investment sub menu item
May 14th, 2025
Introduction
Risk free interest rates vary from country to country, but in order to invest in a different country you must first exchange your original currency into the local one and then reverse the process when you decide to recover the investment.
While you will know the interest rate for your investment in a local risk-free bond, the inflation rates and the future currency exchange rates remain uncertain.
You may correctly think that you could use currency futures to reduce the uncertainty. However, future currency exchange rates are set such that it would not matter where you invest, you would obtain the same benefit once you exchanged the local currency at the pre-arranged future exchange rate. Otherwise, there would be arbitrage opportunities. Moreover, preset currency futures seldom match the actual observed currency exchange rates, once the future becomes the present.
Situation to be analyzed
Currency futures depend on both the current currency exchange rate and the ratio of the two risk free interest rates. If the risk-free interest rate of country A is always larger than that of country B, that would imply the currency future would always be increasing in terms of number of monetary units of A per monetary unit of B. That has been typically the case of emerging markets compared to more advanced economies. Despite the ever-increasing currency futures in those cases, in actuality currency exchanges fluctuate depending on a number of existing relative forces between countries: commerce, tourism, investments, repatriation of profits, etc.
Careful analysis and monitoring of currency exchanges may result in interesting return opportunities, but just as is the case with public company investments, you must always keep in mind that there may be adverse movements in exchange rates.
Analysis
We will analyze the results of having invested a certain amount of money, as a citizen of each of the following countries in all of the others during two periods: May 2023 thru May 2024, and May 2024 thru May 2025. The countries are:
Eurozone, United Kingdom, China, Mexico, United States and Canada.
Figure 1 shows the actual one-year interest rates as of May 10th for 2023, 2024, and 2025.
Based on the data depicted on figure 1, Mexico would seem like a very promising investment opportunity every year, but back in May 2024 the Mexican Peso was particularly strong and a year later it had lost some of that strength. In effect there are continuous devaluations/appreciations among the different currencies.
Results
Upon analyzing all possible investments for the citizens of each country, it turned out that for all of them, an investment in MXN in a one-year Cete (a Mexican Zero coupon bond) and then converting back to their original currency, would have resulted in the worst return for the May 2024- May 2025 period. Meanwhile an investment in a T-Bill in GBP reported the best returns for that same year. See figure 2.
However, if the period had been May 2023 to May 2024, for all citizens investing in MXN in Cetes would have been the best investment opportunity while a Chinese government bond in CNY would have reported the worst outcome.
As a currency gets stronger it's not the best time to temporarily invest as a foreigner, but to consider changing back into your original currency if you already had investments there.
Conclusion
What lessons may we derive from the preceding?
- Devaluations may offset local relative higher earnings.
- Timing investments and withdrawals is of the essence.
- Careful analysis and monitoring of opportunities may report considerable benefits.
A strong currency is akin to a high-priced stock. Similarly, a temporarily weakened currency is akin to a cheap stock. However, you must always dig deeper and monitor the markets where you are considering investing.
Let us know what you think. Until the next post!
Estimating the loss in intrinsic value as a result of Tesla's revenue slowdown.
Using the Full Sand Box sub menu item
May 7th, 2025
Introduction
Finnugget was designed to help you analyze financial situations and develop plans accordingly. In particular, the full Sandbox menu allows for a complete analysis of financial prospects, which can help you evaluate a listed company as an investment option or act in your own business.
Situation to be analyzed
As an example, we may go over how the recent decline in revenues has affected the intrinsic value of Tesla.
The value of any company depends on its potential to attract revenues, and when the potential decreases, so does its intrinsic value.
Revenues and Trends
The trend analysis for Tesla's revenue, using the yearly data from 2010 to 2023, resulted in the violet quadratic trend shown in figure 1. However, the deceleration in revenues observed in 2024 resulted in a new best fit shown in red, and the recent results for Q1 used to estimate the total of 2025, resulted in the lowest blue trend.
The violet and blue trends will be used to estimate the change in intrinsic value. They will both be used to forecast expected revenues for the decade 2025-2034.
Solution
In the Sand Box menu, enter the real revenue for 2024 and choose constant change in amount of growth. That option is the quadratic change. You will then be asked to provide the values for two parameters. The first-year increment is obtained as the difference in the trend forecast for 2025 minus the trend forecast for 2024. The second parameter is the change in yearly increment. This is always equal to two times the value of the first coefficient in the quadratic equation. That value is 1.93 in this case. It is the constant change in yearly increment in billion USD. Because of the high volatility, it is appropriate to choose a 0.4 randomness level (this value varies between 0 and 1). Next, you will be asked for the parameters that enable obtaining the free cash flows. We consider costs and expenses to be the average, as percentage of revenue, of the values observed in the period 2022-2024. That is 79.4% for costs and 9.2% for expenses. Income taxes was set equal to 1.8% of revenues (the most recent value) and Capital Investments was set to 10 billion USD per year.
Next, you have to enter the number of existing debt contracts. The following data was obtained from form 10K for 2024. All of the contracts were assigned an estimated interest rate of 3.74% because no specific interest rates were found.
| Contract |
Principal |
Maturing in |
| 1 |
2.353 |
1 year |
| 2 |
4.116 |
2 years |
| 3 |
0.699 |
3 years |
| 4 |
0.243 |
4 years |
| 5 |
0.095 |
5 years |
| 6 |
0.401 |
after 10 |
For the required discounting rates, the following were used:
| Year |
Interest |
Year |
Interest |
| 1 |
5.11% |
2 |
5.23% |
| 3 |
5.42% |
4 |
5.64% |
| 5 |
5.8% |
6 |
6.07% |
| 7 |
6.25% |
8 |
6.48% |
| 9 |
6.65% |
10 |
6.89% |
Assigning a linear growth perpetuity of 10 billion per year (25% of the difference between 2034 and 2033) resulted in a 926.48 expected intrinsic value, or 289.64 USD per share. The sand box menu enables comparing those results to another scenario, so repeat the process with the updated revenue projection and keep all percentages, debt and discounting rates the same. This results in a new value of 665.99 billion or 208.32 USD per share. The valuation comparisson is shown in figure 2. The new value per share is basically 72% of the value reported with the original 2023 trend used to forecast revenues. However, notice that because of the uncertainty in the forecast, the price per share in this latter version could go all the way down to 552.18 billions or 172.72 USD per share. The number of shares used was 3.197 billion.
Disclaimer
The data used in this analysis is publicly available. The results reflect our opinion and our approach to valuing a company. Any person using these results is solely responsible for his or her actions. Investments in public companies always entail risk.
Conclusion
Intrinsic values are dynamic in nature and quite sensitive to a company's actions. It may take a long time to build a very good customer relationship, and that is something to be treasured.
The Sand Box menu is a powerful tool for financial planning and analysis.
Let us know what you think. Until the next post.
Using Swaps to reduce the uncertainty of future costs
Using the Swap sub menu item
April 22nd, 2025
Introduction
Swaps are very useful financial instruments that allow transforming one set of future cash flows into a different but equivalent set of future cash flows.
Problem Analysis
Consider an electronics manufacturer located in Mexico requiring 2.5 kgs of gold every quarter. In order to be able to eliminate uncertainty in its production costs the company could enter into four future or forward contracts, but this implies a different cost per gram each quarter. However, if the company also enters into a swap contract, it could set it up to receive the future value of the gold in exchange for paying an equivalent fixed amount over the following four quarters.
Information required
The necessary information is the current price per gram of gold: $2,101 MXN and the four risk-free quarterly interest rates currently available in Mexico. The equivalent of a Treasury Bill in Mexico is called a CETE. We used the data posted by Mexico's central bank, Banxico, to estimate the 4 effective quarterly interest rates that would be needed in the Swap contract.
| Quarter |
Effective
rate |
| 1 |
2.1575% |
| 2 |
2.1075% |
| 3 |
2.125% |
| 4 |
2.1425% |
Solution
With the above data, you may use finnugget's Swap sub-menu to obtain the payment amounts for the four forward contracts and the equivalent fixed amount, which is $5,535,365.82 MXN. See figure 1.
Conclusion
Swaps may be used for a variety of purposes, ranging from reducing financing costs to transforming time-dependent cash flows into constant amounts. The preceding example shows one possible use of this powerful financial instrument.
Let us know what you think. Until the next post.
Arbitrage opportunities with Forward contracts on Gold
Using the Forward price sub menu item
April 11th, 2025
Introduction
Gold is a precious metal with multiple uses ranging from investments to electronics, to jewelry to aerospace. It used to be the underlying medium of exchange until Central Banks stopped using it as a standard. But Central Banks still keep gold as part of their reserves, and places like Fort Knox hold large amounts of it.
It is interesting that during periods of peace and stability, its price doesn't vary by much, but because us human beings are prone to conflict, eventually markets crash and gold price rises.
We may easily observe, from figure 1 below, how the price of gold rose after the financial market crash of 2007-2008 and how current political turmoil has also resulted in a considerable price hike.
Preparing the Analysis
According to https://goldprice.org/ if you had kept an investment in gold over the past 20 years, you would have obtained a 638% benefit.
Pretty impressive as long as you knew when to enter and exit the investment! After the peak in gold price in 2012 there was a considerable drop in its price in the span of a year. But enough of looking backwards. What may happen in the future? and how do we analyze potential investments?
The future is uncertain, but there are Options, Futures and Forward contracts. As of today, April 11th, the price of an ounce of gold ranged from 3,193 to 3,263 USD. Looking at the futures contracts the one expiring in one-year GCJ6 at CME, had an open price of 3,356.0, a daily high of 3,362.7 and a low of 3,339.7 and 118 contracts, each for 100 ounces, were traded. So, the price is expected to increase, but not in keeping with the recent growth trend. Why? Because future's prices should not give opportunity for setting up an arbitrage.
Futures and forwards are similar instruments, but positions in futures' accounts are adjusted daily, margin calls may be made, and positions are often closed prior to the termination date. However, let us look at how we could use forward contracts to arrive at a fair future price of gold.
Forward Contract Arbitrage Opportunities
Assume that the current price you are analyzing is right in the middle of the current price range observed today: 3,228.0. Consider neither storage nor transaction costs and a quick search lets us know that in the US the one-year treasury bill interest rate is currently 4.048%
With the preceding information, go to the Forward prices sub-menu and, after providing the current price, choosing 'asset', 'compute' and providing the interest rate to maturity you get that the arbitrage free price in a year is 3,358.67 per ounce.
What if you could find someone willing to enter into a forward contract with you for a different future price?
The arbitrage free price is very close to the future open price observed today at CME, so let's consider both the higher price and the lowest.
If you enter the 3,362.7 forward price, you get the result shown in figure 2. You could set up an arbitrage by borrowing money at the risk-free rate, use that money to buy gold keep it for one year and then deliver it and obtain the agreed upon forward price per ounce. With that money, you would have enough to pay back the debt and keep an arbitrage. If you use 1 contract (100 ounces) the arbitrage is 403.1 or 0.12% with respect to the amount you borrowed.
On the other hand, if you enter the 3,339.7 forward price, you get the result shown in figure 3, where you would have to play the opposite role and make an arbitrage of 1,897 USD or 0.59% with respect to the amount you shorted.
Conclusion
Yes, the price fluctuations could result, in theory, in potential arbitrage opportunities but the benefits are small given the current daily level of price fluctuations. Future and forward contracts are not used so much to speculate as options may be, and they play a crucial role in guaranteeing input prices or revenue levels. On the other hand, there are currently one year call options being actively traded on gold with strike prices of 3,700 USD.
Let us know what you think. Until the next post.
Trading strategies: Bear Spread with Tesla Options
Using the Payoff function sub menu item
April 2nd, 2025
Introduction
Options can be used to hedge against variations in prices of its underlaying asset, or to speculate on what future prices may be. Among market participants speculators play a very important role by contributing to the liquidity of the various available instruments.
In recent weeks there has been considerable speculation regarding Tesla's shares. Its 52-week high was 488.54 USD and its 52-week low was 138.8 USD, while today's closing price was 282.76 USD
Consider a speculator who believes that the price is going to keep on decreasing, but who also wants to limit her potential losses to 100 USD, in case her intuition turns out to be wrong. Then, she could set up a Bear spread with call options as follows: Buy five call options with strike price 285 and sell five call options with strike price 245.
A Bear spread is set up by buying a call with a higher strike price, than the strike price of the call you are selling.
A Bear spread may be easily computed using finnugget's Payoff function submenu.
Preparing the Analysis
Prior to using the Payoff function submenu, a few values need to be computed. First, use the three-rate menu to estimate the effective interest rate from the current yearly nominal rate of 4.34%. It is readily found to be 0.948% for the period ending on June 20th. Next, use the Value Put or Call submenu and use it to estimate the Standard Deviation of the log of the rate of growth. This is accomplished by loading the historical data for Tesla. The problem here is to determine how far back in time should we go. Using the most recent 3500 days you will obtain one estimate, but if you use 3000 days you'll get another and by using 2500 days yet another. Then, you must decide whether to use the opening price or the daily closing price. The two daily prices will likely generate estimates with small variations. How to solve this problem? In our experience using 3000 days, which is roughly equivalent to 8.2 years, seems about right and easier to remember than the 2922 days needed for 8 years.
Use Stock History in Excel to get the opening prices since January 14, 2017, until April 1st, 2025. Upload that file, to get the standard deviation. The result is 0.64003 Then compute the price of an American Call for TSLA with initial price 282.7 and strike price 245, which is 55.49 USD and if the strike price is 285, the result is 34.07 USD
We can compare the above results with the prices reported by Yahoo Finance for the two corresponding contracts: TSLA250620C00245000 and TSLA250620C00285000 See figures 1 and 2. The standard deviations reported are higher than the one we used, but the price variation throughout the day, in both contracts, was quite significant: from 38.1 to 58.39 for the contract with strike price 245 and from 21.91 to 36.7 for the one with strike price 285. Both of the prices we had computed are contained in those ranges.
The only thing missing is the time in years until expiration. There are 56 trading days (until June 20th because there are two holidays), then divide that number by 252 corresponding to the number of trading days in a year. The result is 0.22222
The Bear Spread
We are now ready to enter all of the data. To setup the Bear spread consider buying 5 Calls with strike price 285 and selling 5 Calls with strike price 245. Enter 282.7 as the current price. The results of the Bear spread are shown in figure 3. Notice that the maximum benefit would be 106.71 USD if the price of Tesla dropped to 245 or lower. The maximum loss occurs at a price of 285 signifying a net loss of 93.28 USD.
Figure 4 shows the price where the net benefit is closest to zero, at a price of 266.3 USD. Any price higher than that value generates a net loss, any lower price, generates a net benefit. No transaction costs were considered in this exercise.
As a final consideration, the Payoff function requires all options to be of type European, but you may use American Calls as well because it may be proved that it is never a good strategy to exercise an American call prior to its expiration date.
Conclusion
Options may help us in setting bounds to potential future prices. It is easy to construct Bull, Bear or Butterfly spreads, or any other spread that may satisfy your particular needs.
Can you think of situations where some spread may provide an interesting solution? Let us know by entering the main menu and sending us an email through the contact form. Until the next post.
Valuing Options, an example with a share of Apple.
Using the Options menu item
March 26th, 2025
Introduction
Options are among the most interesting financial instruments. They are contracts with a buyer and a seller.
The buyer of the option obtains the possibility of guaranteeing either a buying price (Call contracts) or a selling price (Put contracts) of a certain asset, called the underlying asset. The buyer acquires the right to exercise the option, and so a date must be set called the expiration date. If the buyer may exercise the option only on that expiration date the option is called European. If the buyer may exercise the option at any time up to that expiration date, then it is called American.
The seller of the option gets an immediate benefit: the option's price but has to deliver the option and get an agreed upon price for it, in the case of the Call contract, or buy the option at the agreed upon price in the case of the Put contract. The agreed upon price must be set on the contract and it is called the strike price.
Exploring Options
It sounds pretty convoluted, doesn't it? But it gets easier if you just think of options as insurance. The seller of the option is like the insurance company and the buyer is just someone wishing to guarantee a certain selling (Put) or buying price (Call). Puts and calls are the two types of insurances, and they come in two varieties American or European. The thing that you want to be able to buy or sell, known as the underlying asset has to be something that is being traded in a financial market, it could be a commodity, an exchange rate for currencies or a share of a public company.
In the case of a public company, the company itself may be completely unaware that its shares are being used as the underlying asset; however, it is also quite common to find public companies incentivizing key employees by offering them options to buy shares of the European type.
How do you set a price to such a complex instrument? Options have been traded for a long time, but what we could term a fair price has existed only since the last couple of decades of the previous century. The way the price is computed is by ensuring that you could not get a financial arbitrage from a portfolio set up with the option, the underlying asset and a risk free bond. Fortunately, finnugget computes that price for you.
The price you pay for an option will depend on several factors, among the most important ones are: time until the expiration date, the strike price, the current price, the risk-free interest rate and also how much the price of the underlying asset has been changing.
An Example: An Apple Call Option
As an example, let's consider the price of an American Option to buy a share of Apple with expiration date on April 4th. That date is 7 days away from today, the current risk free interest rate in 4.5% per year with daily capitalization, for all other required parameters, we may use the STOCKHISTORY function in Excel and find the daily closing price of Aapl for the last 3000 days (how far back you go is pretty much up to you!). We upload the data into finnugget's Option Value menu and as soon as we input 222 for the current price and 200 as the strike price, we get the results shown in figure 1. The price of an American Call with strike price 220 and current price 222 is today, 5.62 USD.
If you looked for the price of such an option in Yahoo finance, you would get the data shown in figure 2.
In particular you may see that the price of the option has been fluctuating between 5.1 and 7.1 during the day, 1,524 contracts (each representing 100 shares) were bought or sold, and there are 6.66 thousand contracts that have not been settled or closed out. So, that gives you an idea of the importance of the derivatives market. In just one type of contract (AAPL250404C00220000) in one day of trading 77,620 shares of Apple were used as the underlying asset.
Conclusion
Options may help us in setting bounds to potential future prices, they may be very useful in hedging strategies, to create incentives, or as part of a merger and acquisition strategy.
With finnugget you may compute the price of any option for any underlying asset, just prepare the data and input it. We'll take care of the number crunching for you. If you have comments or suggestions, we're looking forward to reading them. Please go to the Contact menu and drop us a line. Until our next posting...
Investment Analysis in Stock Markets
Using the Investment models menu item
March 19th, 2025
Introduction
Public companies have two prices attached to them at all times. The first one is readily available as it is its current market price. The second one is harder to obtain and is known as its intrinsic value. A company's intrinsic value represents its current potential to generate net benefits expressed as net present value and divided by the number of shares outstanding. Public companies have two prices attached to them at all times. The first one is readily available as it is its current market price. The second one is harder to obtain and is known as its intrinsic value. A company's intrinsic value represents its current potential to generate net benefits expressed as net present value and divided by the number of shares outstanding. Clearly computing the second price involves number crunching and it's time consuming.
Anyone with a mild interest in public companies, knows that their prices fluctuate quite a bit. People refer to that fluctuation as volatility and that means there is no certain outcome. Furthermore, you may have heard that every now and then markets crash (1929, 1987, 2007), so there is certainly risk involved in these kinds of investments, but the flip side is that there is also opportunity. A share of Apple in January 2000 was worth 0.94 USD. That share today is worth 212. So, if you had invested 1000 USD in AAPL back then, you would have 212,000 USD on Monday March 18, 2025. That's almost a 24% yearly compound growth! But GE was also considered a solid investment in 2000, and it pretty much ceased to exist as we knew it.
Investing
So, there are at least three lessons here: First you should study the companies that you are planning on investing. Two, diversify. Three: monitor and adjust your investments periodically. Having said that, we can go deeper. Assume you are considering investing in the following five companies: Amazon, Apple, Google, Microsoft and Meta. Because of the recent decrease in their prices, you think it may be a good time to buy some shares, but you don't know how much to invest in each.
Company
Ticker: | Current
Price | 52 week
Low | 52 week
High |
|---|
| AAPL | 212.7 | 184.1 | 260.1 |
| AMZN | 192.8 | 151.6 | 242.5 |
| GOOGL | 162.7 | 147.0 | 208.0 |
| META | 582.4 | 414.5 | 740.9 |
| MSFT | 383.5 | 376.9 | 468.3 |
Fortunately, you may use Finnugget , but first you have to prepare the input data, using Microsoft Excel's Stockhistory function or a similar one in another spreadsheet software. You could basically obtain, in 15 minutes or less the monthly closing prices and in the bottom row the current stock outstanding (in billions). As shown below in figures 1 and 2.
Middle part of table not shown.
Once you have the data in the format above, you may upload it directly into the Mean Variance menu and you will get the chart shown as figure 3.
Strategy
Clicking on anyone asset you may see its ticker, its monthly estimated expected rate of return and its estimated monthly standard deviation. Figure three shows META.
By clicking on the efficient frontier radio button and leaving the is shorting permitted box blank, you get the chart appearing as figure 4.
You may click on any point in the frontier and get the corresponding investments that would generate the estimates for both expected rate of return and standard deviation. In the chart we chose a portfolio with a monthly expected rate of return of 0.023 and a standard deviation of 0.074 Such a portfolio, shown in green, would have the following composition: 8% of the amount you intend to invest should be invested in MSFT, 60% in AAPL, and 32% in META. Of course you could incorporate other companies up to 10 of them. You could just as well analyze companies from other exchanges around the world.
As an example from Indian companies, we analyze a potential portfolio using data for HCL Technologies, Bharti Airtel Ltd, Tata Consultancy Services Ltd, Sun Pharmaceutical Ind. Ltd., Reliance Industries and Infosys Ltd. We obtain the following chart in which we chose the point on the efficient frontier with smallest standard deviation yielding the following portfolio composition: 36% in BHARTIARTL, 40% in SUNPHARMA, 24% in INFY. See figure 5.
Conclusion
Always keep in mind that investments in the stock market carry risk, and that they are meant to be long term investments. They may be sound investments as long as the company is well managed, with outstanding services or products and also that you are able to explain to someone else what is it that the company does and why it appeals to you. If at any point in time you are not comfortable with the way the company is being run, its products or services, or you are no longer able to explain either what it does or why it appeals to you, it may be time to explore other investment alternatives. Hopefully, Finnugget will help you out again.
Cash Flow Transformations in Debt Management
Using the Equivalent Cash Flows menu item
March 12th, 2025
When analyzing a project's attractiveness, it is easier to decide based on a single equivalent cash flow, rather than trying to interpret a set of future cash flows. Such transformations are made possible using the set of appropriate interest rates. The interest rates must span the project's horizon.
The equivalent current cash could very well represent the funding necessary to meet certain future obligations. That would be the present value of the future obligations. In that case, the set of interest rates would be the ones that could be obtained for all future payment dates from the best available instrument, such as a set of riskless or high-quality zero-coupon bonds.
For example, Microsoft reported in June 2024 total debt of almost 45 billion USD. That debt included 4 bonds with the data shown in the table below:
| Issued on: | Principal | Yearly
Coupon | Maturity |
|---|
| 2009 | 3.8 | 5.2% | 2039 |
| 2010 | 4.8 | 4.5% | 2040 |
| 2011 | 2.3 | 5.3% | 2041 |
| 2012 | 2.3 | 3.5% | 2042 |
Let's assume yearly coupons are payable in March 15 and that Microsoft considers high quality bonds to offer and acceptable level of risk and of course better interest rates than government securities. The interest rates for high grade bonds and the obligations derived from these bonds until their maturities are shown in the table below:
| Year | Interest
rate | Payment | Year | Interest
rate | Payment |
|---|
| 0 | 0 | 0.616 | 9 | 0.0536 | 0.616 |
| 1 | 0.0458 | 0.616 | 10 | 0.0544 | 0.616 |
| 2 | 0.0467 | 0.616 | 11 | 0.0553 | 0.616 |
| 3 | 0.0476 | 0.616 | 12 | 0.0561 | 0.616 |
| 4 | 0.0486 | 0.616 | 13 | 0.0568 | 0.616 |
| 5 | 0.0496 | 0.616 | 14 | 0.0574 | 4.416 |
| 6 | 0.0507 | 0.616 | 15 | 0.0579 | 5.2184 |
| 7 | 0.0517 | 0.616 | 16 | 0.0583 | 2.5024 |
| 8 | 0.0527 | 0.616 | 17 | 0.0586 | 2.3805 |
We may easily input that data, using the required format, into the Equivalent Cash Flows menu item and find that the present value of those obligations currently is 12.48 billion USD. See figure 1.
It is just as easy to transform that cash flow into a currently equivalent one in March 2042, that future value is 32.87 billion USD. See figure 2.
Finally, we could transform those obligations into a set of eleven equal payments of 1.44 billion USD beginning now and ending in ten years, March 2035. See figure 3. Why would it be important to consider different sets of transformations? What do you think are the factors involved? How about getting rid of old bonds paying more sizable coupons? When would that be a relevant issue? What interest rates should a company use in the transformation process if it is not considered a high-quality credit company? Should it use one set of rates for investment and paying purposes and another set of rates for other transformations?
Conclusion
Any set of future cash flows may be easily transformed into an equivalent one through the use of the appropriate interest rates, but What do you think happens as interest rates change? We'll pick up this conversation in a later posting.
Term structures of interest rates in financial planning
Using the Term Structure menu item
March 4th, 2025
Interest rates constitute the most important price, typically expressed as a rate, when considering loans or investments. This price represents the cost of financing or the potential benefit to be derived from investing between any initial and final times, and in financial planning it is used to estimate what those financial costs or benefits could be for future periods.
For example, if you knew today, that you will receive an important amount of money, say 1 million USD in one year, and you do not expect that you will need it for another two years hence, could you today make arrangements that will insure how much you will have at your disposal three years from now? The answer is most definitely Yes!
Background
The Federal Reserve Bank of Saint Louis reports every month, the 'spot' rates for high quality rating bonds in the US. High quality means bonds issued by companies that are currently highly unlikely to default on their obligations. These are companies whose obligations are not entirely risk free, such as are the federal government issued obligations in any given currency. But they are companies for whom paying off their debts should not cause them any difficulty whatsoever.
The high-quality 'spot' rates, for January 2025 are shown in figure 1, along with the corresponding rates for January 2024. A given set of 'spot' rates gives rise to a complete set of future implied interest rates. Here, we will show how to make those future interest rates available to you at the same time as those 'spot' rates. Both the 'spot' and the future interest rates constitute what is known as the term structure of interest rates at any given time for a given level of risk.
Solution
Those future interest rates play a fundamental role when negotiating terms for various transactions to take place in the future. Figure 2 shows the implied future interest rates that the Term Structure menu item computes for you.
To verify that you could set up a contract today that guarantees that you will be receive anyone of the implied future interest rates, take a look at figure 3, which shows how you could guarantee the 4.85% interest rate between years 1 and 3 (i.e. 'future 1,3' from january 2026 till january 2028). You could repeat the exercise with any initial and final times. Of course, we assume that shorting any fractional amount of any bond is possible. Bond A in this example is a zero coupon bond maturing in one year, and bond B is a zero coupon bond maturing in three years. Both bonds with a 100 USD face value.
![Setting a portfolio to guarantee F13 rate]()
Investing one million in one year would require you to short 10,000 bonds A sell them today at their price today which is $95.62058 USD each and invest the total of $956,205.8USD buying 10,993.548 bonds B (at 86.9788 USD each). Notice you don't really put any money initially, but you owe 10 thousand bonds A and that means one year from now, in January 2026 you will have to pay $1million. But that is the amount of money you will receive then. Finally, in January 2028 you will get paid $100 for each of the 10,993.548 bonds B you bought. That is $1,099,354.8 USD Meaning the interest you obtained on the $1 million you paid in 2026 is 99,354.8 or 4.85% per year for each of the two years. But that is precisely the future rate in figure 2 between year 1 and 3!
Conclusion
Term structure of interest rates offers the possibility of managing risk by planning what future income or obligations will entail in terms of monetary flows. You can do that as long as there are 'spot' rates available with a horizon encompassing your planning needs.
How Good is an investment in government bonds in the US and Mexico today?
Using the Three rate menu item
February 24, 2025
A key aspect when analyzing investments, is to understand the difference between effective and real interest rates for a given time period. Those two rates are related by the inflation rate. Let's look at the potential benefit of investing in a 'treasury bill' in the US and a 'cete' in Mexico, for one year, in order to look at how those interest rates are related. Both the 'treasury bill' and the 'cete' are zero coupon bonds with a given maturity.
According to the results of the auction reported by the Department of the Treasury of the US, a 182-day Treasury Bill was priced today at 97.8868 USD and will pay 100 on August 28, 2025.
That means that each Bill will pay an effective rate of 2.1588% for one semester (26 weeks out of 52 in a year).
In order to analyze a complete year we need to assume that the bi-annual rates will remain pretty much the same for the next semester. With that assumption, we may use the three rates menu and enter 0.021588 in the effective rate input, and then select the bi-annual radio button in the Choose capitalization frequency table. Enter 2 in the number of periods box, and then using the expected inflation rate of 3% for the next 12 months, from the New York Federal Reserve Bank, we arrive at a Real interest rate of 1.3244%
That is the growth in purchasing power that investing in bi-annual treasury bills over the next twelve months would give you. So, you would have 4.36% more money, but things are expected to be more expensive resulting in just 1.3% of purchasing power improvement. See figure 1.
![using three rate menu with 1 semester bill]()
Of course, you may as well look for a one-year treasury bill, but the most recent auction for 52 weeks maturity bills was held on January 21,2025 and at that time the price from the auction was 95.93028 That meant a 4.24237% yearly effective rate, and with a 3% inflation resulted in a 1.206% real interest rate. See figure 2.
In the case of the Mexican 'cete', the auction of february 20 for a 350 day bond resulted in a price of 9.16999 for 10 MXN to be received in february 5 2026. That means an effective rate of 9.051% for a period two weeks shy of a year. Mexico's Central Bank has estimated the inflation for 2025 at 3.83% If we use that inflation for the next twelve months and take the 50 week Cete as a one year investment, the real interest rate in this case results in 5.47%
However, you must also keep in mind that interest received from investing in either treasury bills or in cetes is taxable. That means a further deduction in your change in purchasing power is applicable.
Conclusion
Investments for a one-year period in US government 'treasury bills' will currently result in an expected improvement of your purchasing power of around 1.2 to 1.3%, probably closer to 1% once IRS deductions have been considered. In the case of Mexico the 5.47% improvement in purchasing power would probably end up being closer to 5% once income tax deductions are taken into account. The higher rate paid by Mexican Cetes also reflects the fact that historically Mexican governments and its financial agent, the Mexican Central Bank have had to incurr in MXN devaluations to be able to meet its obligations. In the US we don't currently see a big incentive for considering investments in treasury bills, and in Mexico it is worth considering as long as the current government manages its public finances responsibly. What do you think?