NMIMS BBA – B.Com Financial Modeling Solved Answer Assignment

Financial Modeling

NMIMS BBA – B.Com Financial Modeling Solved Answer Assignment

1. Using Google Spreadsheet, create a dynamic model for computing beta for any NSE listed Stock. Use monthly historical data of 5 years for beta computation. Use the computed beta to apply CAPM Model and compute required/expected rate of return for an investor. 10 Year govt. bond yield should be used for risk free rate of return and Nifty50 index should be used as a proxy for market portfolio/market. Compare the computed beta with Yahoo Finance Beta. If yahoo finance beta is not available, compare with beta value available on moneycontrol.com. Explain why the two beta values differ.

Ans:

Introduction:

Beta is a widely used measure in finance to examine the systematic risk associated with a specific supply or investment. It suggests the sensitivity of a stock’s returns to modifications in the overall market. By comprehending a stock’s beta, capitalists can evaluate how much risk they are tackling compared to the whole market. In this project, we will develop a dynamic model utilizing Google Spreadsheets to compute the beta for any stock listed on the National Stock Exchange (NSE) based on monthly historical data over five years.

Concept & Application:

1. Computing Beta:

Beta measures a stock’s systematic threat concerning the general market. It quantifies the degree to which a stock’s rate relocates feedback to modify its index. The formula to determine beta is as adheres to:

Beta = Covariance (supply returns, market returns)/ Variation ( market returns).

We need historical information for both the market and the store to compute the beta for a supply. In this situation, we will utilize regular monthly data over five years. Google Spreadsheets provides a range of features that allow us to obtain historic supply costs and index values, such as the GOOGLE financing feature.

Conclusion:

In this job, we have developed a vibrant version utilizing Google Spreadsheets to compute beta for any stock listed on the NSE. We used regular monthly historical data over five years to determine beta and then applied the CAPM model to identify an investor’s anticipated rate of return.

NMIMS BBA – B.Com Financial Modeling Solved Answer Assignment

2. Assuming a role of a risk-averse investor, identify 5 stocks from Nifty 50 and create a portfolio. Compute the portfolio return, portfolio risk, and portfolio beta. Assume equal weight for all asset weight allocations. Use Sharpe Ratio and Treynor’s ratio to optimize portfolio weight allocation for maximizing the Sharpe Ratio and Treynor’s Ratio. Compare the allocated asset weights with the weights of the minimum variance portfolio. Assume a 2% minimum allocation in any of the assets at all points of time for attaining diversification benefits. Present your understanding on reasons for change in weight allocations.

Ans:

Introduction:

Worldwide of financial investments, risk-averse financiers focus on resource conservation over making the best use of returns. They look to lessen the volatility of their profile by expanding throughout different properties.

Concept & Application:

Step 1: Stock Selection

We must pick five supplies from the Nifty 50 index. For this example, allow us to presume the adhering to stocks have been chosen for our portfolio:

1. Hindustan Unilever Ltd. (Durable Goods).
2. Dr. Reddy’s Laboratories Ltd. (Pharmaceuticals).
3. Power Grid Corporation of India Ltd. (Utilities).
4. Oriental Paints Ltd. (Durable Goods).
5. Titan Company Ltd. (Consumer Goods).

Conclusion:

In this circumstance, we assumed the role of a risk-averse investor and constructed a profile using five stocks from the Nifty 50 index. We designated equally.

Weights to each supply and computed the portfolio return, profile danger, and profile beta. We also discussed the concept and application of optimizing weight allocation utilizing the Sharpe Ratio and Treynor’s Proportion, together with a contrast to the minimal variance profile.

NMIMS BBA – B.Com Financial Modeling Solved Answer Assignment

3. a. Create a dynamic loan amortization template with a maximum of 25 Years of Loan and EMI payment. The End user can choose Annual Interest Rate, Loan Term/Tenure, and Loan Amount. For uniformity in evaluations assume 10% Interest, 25 Years Loan, and 50, 00,000 Loan. The model must be dynamic.

Ans:

Introduction:

To develop a car loan amortization layout in Excel, you can follow these steps:

Concept & Application:

1. Open Microsoft Excel and produce a new workbook.

2. Establish the column headers for your car loan amortization table. For example, you can have columns for Settlement Number, Settlement Day, Starting Equilibrium, EMI, Passion, Principal, and Ending Balance.

3. In the initial row of the Settlement Number column, get in “Repayment Number” and fill out the subsequent numbers from 1 to the maximum variety of repayments (25 years x year = 300 rebates).

In conclusion, developing a vibrant loan amortization theme in Excel can be a beneficial tool for businesses and individuals to manage their car loans properly. The template can produce an exact and thorough repayment schedule by enabling customers to input variables such as the annual rate of interest, loan term, and finance quantity.

3. b. Based on the template created in 3(a), graphically present the Loan Repayment Schedule and Interest and Principal Component paid at the end of the tenure of the loan.

Ans:

Introduction:

Loan Repayment Schedule Graph: To offer the financing settlement timetable graphically, you can produce a line graph utilizing the Repayment Number (x-axis) and Closing Equilibrium (y-axis). This chart will reveal the decreasing equilibrium over time as the finance is settled. You can customize the graph by adding axis tags, a title, and any extra format you prefer.

Concept & Application:

Interest and Principal Component Graph: You can create a piled column graph to graphically offer the interest and principal elements paid over the funding period. This chart will present each repayment’s failure into the interest rate and central parts. Each column stands for a payment period, and the elevation of the column suggests the complete repayment amount. You can tailor the chart by including axis tags, a title, and any added formatting you like.

Conclusion:

Developing a vibrant finance amortization theme allows services and people to understand their car loan settlement schedule clearly. By inputting the funding amount, the yearly rate of interest, and the lending term, customers can produce a detailed repayment plan that breaks down each repayment into the interest rate and primary components.

NMIMS BBA – B.Com Financial Modeling Solved Answer Assignment