Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
1answer
113 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, ...
0
votes
0answers
59 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 ...
1
vote
1answer
51 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 ...
2
votes
1answer
139 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 ...
1
vote
0answers
32 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 ...
1
vote
2answers
708 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 ...
1
vote
2answers
177 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 ...
0
votes
1answer
1k 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 ...
3
votes
1answer
90 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 ...
3
votes
2answers
3k 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 ...
4
votes
2answers
117 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 ...
1
vote
2answers
384 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", ...
2
votes
1answer
778 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 ...
15
votes
2answers
23k 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 ...
1
vote
1answer
265 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%...
2
votes
2answers
203 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 ...
2
votes
3answers
933 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 ...
1
vote
1answer
5k 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, ...
1
vote
1answer
101 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, ...
-1
votes
1answer
140 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 ...
2
votes
1answer
3k views

Annual compounding with monthly payments

I'm trying to solve the following problem using the Time Value of Money formulae on MS Excel and getting nowhere. Can someone please help me? X makes monthly payments of $3500 at the end of each ...
1
vote
3answers
422 views

Is this how you do Currency Conversion in excel?

CHF (Swiss Franc Currency) USD (U.S. Dollar) 0.976 CHF = 1 USD 1.025 USD = 1 CHF I have to convert 14,603.39 USD into CHF on Excel. I basically took: $14,603.39 * 0.976 to get CHF 14,252.906 ...
2
votes
2answers
5k views

How to calculate the number of months until a loan is paid off (given principal, APR and payment amount)?

Does anyone know what formula they use for this When will you be debt-free? calculator? I want to put it in a Google Spreadsheet, but I can't find a payments-to-debt-free formula using my Google ...
1
vote
1answer
577 views

XIRR not working for this dataset. Any other method to calculate rate of return?

For this particular dataset xirr is not returning any reasonable value either in excel or google spreadsheet. What is the option to calculate the rate of return in cases like this?? Sl-no date ...
2
votes
1answer
339 views

Excel formula for calculating interest rate of earning

On January 1st I invest 1238. I do this every January 1st for 20 years. At the end of 20 years (So December 31st of the 20th year), I have 32880 in the account. What's my rate of interest? By playing ...
1
vote
2answers
84 views

Translating a List of Monthly Payments from One Currency to Another

I have a list of monthly payments in Excel that looks like this: 01/18/2005 340.00 07/25/2005 270.00 05/10/2006 500.00 03/30/2007 730.00 10/01/2007 120.00 If the payment amounts in the second ...
0
votes
0answers
217 views

Sensitivity Table for IRR Using Purchase Price & Total Synergies

For my DCF I have a line for Synergies to be entered for each of four years How can I create a sensitivity table in Excel for IRR taking into variations in purchase price (enterprise value) and total ...
12
votes
6answers
16k views

Why use accounting software like Quickbooks instead of Excel spreadsheets?

Some friends and I are starting a small landscaping cooperative, and are thinking about how we will manage accounting. Places we've worked in the past have used Excel spreadsheets to manage all of ...
2
votes
1answer
3k views

Calculating total number of monthly payments in Excel using NPER and PMT?

How to calculate the total number of monthly payments given the amount of loan ($275,000), the annual interest rate (4.35%) and the number of years (30)? I'm confused because I'm trying to use the ...
0
votes
2answers
88 views

Find the compound return

If I have a specific amount of money. For example 1000€ at the beginning of the 2008 year. -65.55 2008 91.25 2009 17.74 2010 -42.87 2011 18.32 2012 -18.82 2013 56.57 2014 How much money i ...
3
votes
6answers
2k views

Shared groceries expenses between roommates to be divided as per specific consumption ratio and attendance

My apologies if this question is in the wrong section. Couple of my roommates & I (total 5 people) share the groceries expenses. We record the purchases in an Excel sheet, and also have the ratio ...
4
votes
2answers
741 views

How can I calculate (approximate) the future value of my investments? Is this formula appropriate?

I want to check to see if I'm meeting my investment goals, so I want to project forward my assets given a growth rate and a monthly deposit amount. What is the best formula to use for this? Is this ...
1
vote
1answer
741 views

How and Where can I easily pull data for the Dow 30? [closed]

Where/How can I pull the following data on the DJIA stocks into Excel from the Web that updates every time I hit refresh in Excel? The data: 1. Last 12 months of dividend payments 2. Prices 3. DJIA ...
6
votes
4answers
28k views

What Excel function to use to calculate the return or interest rate on a savings account?

I'm getting confused by Excel's various different financial functions. Which one would I use to answer the following two questions and how? Suppose that on October 1st, 2012 I open a savings account ...
1
vote
1answer
489 views

How can I make Excel handle fractions like Quickbooks?

When you have a fraction in Excel like 1.085 rounded to two decimals = 1.09. In Excel if you add a series of these fractions with displayed two decimals of precision you are really adding 1.085 and ...
3
votes
3answers
713 views

Calculating Future and Present value into mortgage comparisons

I'm looking to buy a home in the next 3 or 4 months. The bank I'm working with has 10 different mortgage rates, currently ranging from 3.5% to 4.625%, with points respectively ranging from 2.875 to -1....
6
votes
2answers
4k views

How can I calculate a “running” return using XIRR in a spreadsheet?

I should say that I'm not married to XIRR, I just want to calculate the return on a running basis in a column next to dates, deposits and balances. Here's a screenshot of a simplified spreadsheet in ...
25
votes
4answers
39k views

Daily interest calculation combined with monthly compounding: Why do banks do this, and how-to in Excel?

I often see banks advertise (for accounts with fixed yearly interest, let's say 1.5%) – "interested is calculated daily, and compounded monthly". I have 2 questions: Why are they calculating ...