Skip to main content

Questions tagged [spreadsheet]

For questions regarding the use of spreadsheet programs (such as MS Excel, Google Sheets, OpenOffice Calc, etc.) in personal finance, including questions about spreadsheet financial functions.

Filter by
Sorted by
Tagged with
0 votes
2 answers
59 views

How can I calculate cumulative rate/gains for retirement fund in a spreadsheet throughout a year?

Novice to retirement funds and investing here. I'm wanting to build a spreadsheet in Excel that can help calculate my annual rate of return for retirement accounts. I have some elementary questions ...
SeligkeitIstInGott's user avatar
1 vote
1 answer
45 views

How can I calculate the time weghted return?

I have switched frequently between ISA providers and I want to see how much my investments have truly increased. I have a spreadsheet where column 1 is the date I invested a certain amount, column 2 ...
TheAnonymous's user avatar
0 votes
0 answers
93 views

How do you measure the Accuracy of Implied Volatility?

Recently I've gotten hold of a list or rather a chart of 30-day Implied volatility and historical volatility for the same stock over the course of a year. With these data, I've thought there might be ...
Telefondemonen_se's user avatar
4 votes
1 answer
776 views

Am I calculating the future payment on my ARM correctly?

We currently have an ARM on our house, bought in April of 2020 with a starting rate of 1.99%. It is up for re-amortization in April 2025. Assuming that the rate adjust to the highest allowed in the ...
Nosjack's user avatar
  • 8,697
3 votes
4 answers
483 views

How can calculate the special cash flow's irr with excel or python accurately?

It's simple to calculate the following cash flow's irr: cash flow date -150000 2023-03-30 575 2023-04-30 594.17 2023-05-30 575 2023-06-30 594.17 2023-07-30 594.17 ...
showkey's user avatar
  • 433
0 votes
1 answer
75 views

Facing problem while creating a Google Portfolio tracking spreadsheet

I am trying to create a Google Spreadsheet to monitor my Indian stocks and Mutual fund investments. I am facing the following issues and hope someone can help me. When I try to track the 'pe ratio' ...
Arvind Gupta's user avatar
2 votes
1 answer
233 views

What is the formula for calculating the max value of a home purchase, including monthly property taxes

How can I adjust the present value formula (PV) to account for also having to pay monthly property taxes? If I know how much monthly payment I can afford (PMT), I can use Excel's PV formula to ...
Brandon's user avatar
  • 121
0 votes
1 answer
6k views

How do I calculate monthly compound interest with additional monthly deposits in Google Sheets?

I'm trying to figure out how to write a formula and/or use a function to calculate the following in Google Sheets. John deposits $1,000.00 into a savings account on Dec 31st. John also deposits $100 ...
Mr. B's user avatar
  • 167
0 votes
1 answer
114 views

How can I track *complex* forex trades so I have an idea of my profit vs loss over time?

What's the best way to keep track of my forex trades so that I can determine how profitable my trading has been? I've been looking into trading foreign exchange currency pairs for profit, but before I ...
Michael Altfield's user avatar
1 vote
0 answers
362 views

Price of Bond with irregular long first coupon period

I'm trying to manually calculate the price of a bond with a long first coupon period, found a few candidate formulas on Microsoft Excel Document: OddFPrice mit.edu: OddFPrice WestClinTech.com: ...
adoDojo's user avatar
  • 11
0 votes
1 answer
101 views

Calculating CAGR from annual investments

Let's say I invest x amount at the beginning of each year. After N years, my total investments are worth y. How do I calculate the CAGR? As a bonus, assume that my only tool is Google Sheets.
relatively_random's user avatar
9 votes
5 answers
4k views

Do bank use the same math formula to calculate the mortgage amortization table?

I've started making a spreadsheet to evaluate mortgages and how they would fit other financial plans. The most complicated part seems to be the mortgage calculation formula. That's the formula the ...
TheMeaningfulEngineer's user avatar
2 votes
1 answer
57 views

Mismatch Between FV Function and the Bankrate Simple Savings Calculator

I'm trying to replicate the functionality of the Bankrate Simple Savings Calculator in Google Sheets. I've assumed that I need to use the FV function, but I can't get the numbers to match exactly. ...
Frank's user avatar
  • 23
0 votes
1 answer
826 views

What is the formula to use to calculate future value of certain money on a certain month from today?

I have an excel sheet like below - In one cell (B1), I would mention the principal value, PV (for which I want to know future value at different point of time). And inflation rate, r, in another cell ...
Darshan L's user avatar
  • 143
2 votes
1 answer
110 views

Calculating an accelerating rate of growth as a formula

Suppose I start with a $1,000 trading account. During each 30 day period my account earns $1,000 in net profit from a trading strategy using one contract per trade. The max amount lost across any ...
eComEvo's user avatar
  • 245
2 votes
1 answer
114 views

What's wrong with the XIRR calculation here?

I am trying to learn how to use XIRR following tutorials.I think I'm overlooking something with this dataset problem when using Google Sheets to calculate XIRR. Can someone be kind enough to explain ...
R11G's user avatar
  • 123
2 votes
1 answer
2k views

Google Sheet formula for annualized returns - doubts

I'm trying to setup a calculator in google sheets where I calculate the future value of investing for example: a principal of 10.000 into which I deposit 1.000 every month the investment doesn't give ...
rudyStock's user avatar
0 votes
0 answers
86 views

Need formula to create credit card payoff spreadsheet that accounts for additional monthly charges

I'm looking for a formula that I could program in a spreadsheet in order to create a credit card payoff calculator that accounts for recurrent monthly charges. I've been searching for days on Google ...
Yanick Girouard's user avatar
1 vote
1 answer
59 views

Mortgage/annuity spreadsheet formula question

I use IPMT formula in google spreadsheets to compute interest payment after 1st month like this: IPMT(annualRate/12, 1, loanYears*12, loanReceived) - this return for my specific case, 40568 loan for 3....
Ghita's user avatar
  • 139
5 votes
2 answers
11k views

Get Revenue Details in Google Sheets using Google Finance

Recently I learned about GoogleFinance and it's feature to use Google Sheets to provide you with all stock market data using a formulae for eg. =GOOGLEFINANCE("GOOG", "price", &...
Aklank Jain's user avatar
0 votes
1 answer
2k views

What is the formula for calculating the months till pay off of a credit card that includes the APR?

I'm playing around in Excel attempting to get the total months to pay off a credit card. I'm not looking to pay off by a certain date. I just want to know how long it will take me to pay off what I ...
user-44651's user avatar
0 votes
1 answer
409 views

Calculate value of investment with variable interest + periodic payments (Excel/Google Sheets)

I know about FV and FVSCHEDULE but how do I combine them? Say I make monthly payments into an account with a variable interest rate ... how do I calculate future value? Or do I have to build out the ...
d0g's user avatar
  • 191
0 votes
1 answer
458 views

IRR calculation with Initial Investment disbursed on two years

In my case, I think: Numbers say more than words. So, I've simplified the financial situation in this image: Here is the Excel file. How to calculate the Internal Rate of Return (IRR) with such a ...
Backo's user avatar
  • 101
3 votes
1 answer
158 views

Importing transactions to be recorded in both 'bank checking' and 'a particular expense' account

I have been recording and analyzing transactions for several years using a spreadsheet. In effect, I have been using a variation of a single entry book-keeping system. I wish to import the data into ...
Norman50's user avatar
0 votes
0 answers
27 views

How much benefit we earn with no cost emi on CC

I am thinking to take a policy on no cost emi of CC. I want to find money I saved annually by opting to no cost emi against paying all premium lumpsum at the start of the year. I want to consider ...
RajS's user avatar
  • 173
1 vote
1 answer
2k views

Daily interest calculation on a 30/360 basis, considering payment occurs before schedule — how-to in Excel?

I'm setting up a payment spreadsheet for a loan just granted, and want to be able to make daily calculations of accrued interest. The debtor has been given a payment schedule with an interest only ...
Miguel Olano's user avatar
3 votes
1 answer
225 views

Is the S&P500's excess return above Treasuries after investing monthly for forty years really 3.6% annualized?

I was playing with Robert Shiller's U.S. Stock Markets 1871-Present and CAPE Ratio dataset to simulate an investor who buys the S&P500 with their monthly savings—equal to that month's CPI—for the ...
Ahmed Fasih's user avatar
0 votes
2 answers
103 views

tracking a portfolio's annualized return

I’ve created a spreadsheet that analyzes the total return of my portfolio in various ways. Among the metrics: A graph that tracks the annualized rate of return. For example a year ago the portfolio ...
Jim V's user avatar
  • 1
0 votes
1 answer
2k views

Monthly Compound Interest calculation

I am terrible at finance and math so this really hurts my brain but I want to work out the compounding monthly interest for an initial amount over x days. For example I have $1500 over 95 days at 10%....
Thomas Byy's user avatar
0 votes
1 answer
107 views

How do I calculate an average monthly income, with accurate yields, from dividend stocks paid quarterly and monthly?

I have a column that has evolved over months and I have another spreadsheet with my positions (my stocks, # of stocks, dividends payout and dividend yield). Those won't move. However, some of my ...
Jeremie Dumas's user avatar
1 vote
1 answer
151 views

How to adjust for long term inflation in an investment plan

So I have my investment Plan set in March 2019, with initial portfolio value of 250.000€, annual return 11% and long-term inflation rate at 1.5%. As you can see in column A, I have Capital BoP, ...
Saverio's user avatar
  • 21
0 votes
0 answers
76 views

How do I solve this in excel? I think it's a cumulative rolling percentage problem

I have a spreadsheet that shows the following columns. Date, Loan Applications Received, Interest Rate Offered, Cumulative Applications Received, Loans Originated, Average Interest Rate on ...
Andrei's user avatar
  • 1
1 vote
1 answer
76 views

Is there a way to track earnings from US stock markets and UK stock markets on a single spreadsheet?

If you trade in stock markets from different countries, how do you usually keep a journal? Do you track everything in one document, or do you use separate documents? I'm not sure how to go about ...
LondonGuy's user avatar
  • 113
2 votes
1 answer
612 views

Building Marcus Savings Calculator in Excel

I'm trying to do some analysis in Google Sheets which involves putting money in Marcus' High Yield savings account. For some reason, I can't seem to get my numbers to match the values from their ...
Prakhar's user avatar
  • 135
1 vote
0 answers
45 views

How to calculate the expected standard deviation of a weighted composite portfolio?

So my questions asks me to determine the expected standard deviation for a weighted composite portfolio, but I have no idea what equation to begin with. I have answered A, but B I am lost. Here is ...
Shane Fellon's user avatar
1 vote
2 answers
7k views

How do I convert a 4% per annum interest rate, compounded semi-annually, to a monthly rate?

Trying to develop a budget model in Excel. I know a prospective savings account makes 4% per annum, compounded semiannual. I'd like to know how much this means it earns monthly. Does it even earn ...
aero26's user avatar
  • 111
3 votes
2 answers
388 views

Compare my variable portfolio performance with S&P 500?

The question may appear simple at first but I really cannot come up with a quick and easy way to compare my performance as a trader Vs S&P500. For the sake of simplicity, I am analyzing my ...
Cool_Coder's user avatar
1 vote
1 answer
8k views

IRR Calculation with multiple investments

Most of the times, most of the searches are returning the IRR calculation just based on a single initial investment. Imagine you have a plan that asks investors to supply money not only once, and you ...
Kasrak's user avatar
  • 113
3 votes
1 answer
345 views

How can I calculate the annualised growth rate for an irregular investment?

I have a portfolio, which I invest in monthly. Depending on my circumstances, the monthly amount changes from time to time (perhaps every two years or so). I want to calculate the annualised growth ...
Mark Barnes's user avatar
3 votes
2 answers
7k views

Compound daily interest with monthly deposits over x amount of years in excel

This is compound interest calculated daily and topped up with deposits every month e.g Begin with $100, earn 2% interest earned daily even on weekends, and then come month end invest another $100 on ...
Traders Day's user avatar
4 votes
2 answers
248 views

Daily Interest Monthly Compounding Multiyear

I downloaded the Excel spreadsheet mentioned in the accepted answer to this question: Daily interest calculation combined with monthly compounding: Why do banks do this, and how-to in Excel? It was ...
Lizzie's user avatar
  • 41
1 vote
2 answers
680 views

Why does =googlefinance() return a different open and close value on a non trading day

I'm using google finance in one of my sheets and it is returning slightly different values for open and close when stocks are not traded on that day. eg. sunday =INDEX(GoogleFinance("AMZN", "open", ...
Ryan's user avatar
  • 113
2 votes
1 answer
930 views

Excel calculating interest rate on loan

Not sure if this is the right place to ask this type of question, apologies if it isn't. I am following this explanation on office support to answer my own question. Using the values they use, what ...
Ron David's user avatar
19 votes
2 answers
74k views

How do I calculate monthly compound interest in Google Sheets?

I have a financial spreadsheet with a column of all my deposits into my savings account. The leftmost column lists the dates of the deposits. Now, let's say my savings balance is $100.00 and my ...
grgoelyk's user avatar
  • 301
1 vote
1 answer
556 views

Help me understand and calculate credit union interest in Google Sheets

I have opened a savings account at a credit union and deposited $100. Every month my account tells me I've received $0.02 in interest, and that my deposit dividend is 0.250% and my APY earned is 0.23%...
grgoelyk's user avatar
  • 301
2 votes
2 answers
563 views

Clarify Microsoft's explanation of MIRR

I don't understand Microsoft's MIRR example. The second parameter is the finance_rate: Finance_rate Required. The interest rate you pay on the money used in the cash flows. The example then ...
Karl's user avatar
  • 131
2 votes
3 answers
2k views

Looking for Excel formula to calculate the benefit of making additional payments on a loan with interest daily compounded monthly

I'm looking to make extra payments to my student loans. I currently owe $30,000 at 5.25%. I pay $250 on the 15th of every month. My loans accrue interest daily that are then compounded monthly. I want ...
Muse879's user avatar
  • 37
2 votes
2 answers
27k views

How do I calculate portfolio standard deviation in Excel?

How do I calculate a portfolio standard deviation given a return? If I have 3 portfolios with similar size of $300 million. Portfolio A : Expected Return % / Dollar Amount : 3.78% / $11.34m, ...
Fabian Tan's user avatar
1 vote
1 answer
110 views

Determining the interest rate necessary to retain a certain balance after sixty monthly withdrawals [closed]

I have seen many examples online for "Time Value Money" and "sinking funds". I want to know how I can combine any type of solver in the PMT-PV equation? Because all these examples have known FV, PV, ...
aghaux's user avatar
  • 113
-1 votes
1 answer
179 views

Is it possible to import bulk historical prices from yahoo or google into Excel and calculate 2STD?

I believe a version of this question has been asked before. However, I'm not sure if what I am trying to accomplish can be done. I am trying to import into Excel at the end of each day all of the the ...
Kevin H's user avatar