1. apgmin


    If I Deposit $ 250 in a scheme, I get returns as under Cell value of Year 1 = A1 what is the yield of the scheme YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 YEAR 6 YEAR 7 YEAR 8 YEAR 9 YEAR 10 TOTAL 0 0 0 4.30 28.61 44.52 65.44 100.42 112.12 374.89 730.30
  2. P

    Help looping a form

    I work for a company that packages numerous products with different brand names. Sometimes we package the same product under a different brand name but the Lot number, expiration date, and product code are still the same. Because of us packaging product this way, this is considered a split lot...
  3. W

    Excel VBA Cubic Spline Interpolation

    Hi Guys, By referring to the older post, I found something below as Cubic Spline Interpolation. The formula I found was s(x) = a(x-xi)^3+ b(x-xi)^2 + c(x-xi) + d, I would like to understand how it translate to the algorithm below. Much Appreciate. Function spline(periodcol As Range, ratecol...
  4. M

    If this then that, but if not that then this

    <tbody> A B C 1 Month Data 2 Aug 3 July 4 June 6.12 </tbody> I'm trying to write a formula that will pull in the most recent data if available. So if C2 is empty, it will return C3, but if C3 is empty it will return C4. Thus, the formula would yield 6.12 in the example...
  5. X

    How To Sum Conditionally?

    Dear ALl, I do have a small sample data set across A1:B10 as follows. I would like to conditionally sum as per the desired result across C2:C10. Could somebody help me out with the formula which can yield the same? <colgroup><col span="2"><col></colgroup><tbody> Region Identifier Desired...
  6. M

    cell("filename") oddly linked among many sheets

    I have this function =cell("filename") on a number of sheets. In a new workbook, each sheet will yield a different value as the sheet names are different. However, I have this one weird workbook where the value of all the cells with this formula will yield the value of the last visible cell...
  7. B

    Calc new Long/Lat based on centroid Long/Lat

    Hello all, I have a centroid (center pivot) with a known Longitude/Latitude. I have a distance, in feet, away from the known center point for both Latitude(X)/Longitude(Y). I need to calculate the new Latitude/Longitude for each point so that I can create a yield map. In the example below, I...
  8. G

    Finding last value using only moving averages.

    Hi guys, I'm trying to find the latest monthly value but I only have access to data that represents a moving average of the last 12 months. What operation should I do to extrapolate what the last monthly data point is? Example: <colgroup><col width="64" span="24" style="width:48pt">...
  9. D

    #div/0! error message

    Hi All, Could someone advise what formula can be applied in order to remove the #div/0! value but still perform the underlying calculation? (Bond price @0% yield). The formula works for all values (%) from 1 to 20. (not sure how to add excel file or screen shot) The below list is how my s/s...
  10. A

    Count Consecutive To Start A Row

    I am trying to count the longest consecutive streak to start a row. For Example: (10 Columns) W W W L W W W W W L would yield 3 W L W W W W W W W W would yield 1 I have a formula to determine the longest consecutive streak but it will find it from anywhere in the columns not just until...
  11. G

    How to AVERAGEIF Max of 2 columns values per row

    Hi, I'm trying to find a way of doing an average of the last column (assuming the max column isn't there). So averageif (max 2 values per row) for the entire range assuming that the result of the max isn't 0. So averageif NOT 0 and the max of the 2 values of Yield to maturity column and 12 month...
  12. F

    Lookup based on max value of a dated range

    Hi All, Ok - my head hurts (sadly not through overindulgence - although I'm considering it as a better option than trying to get my brain around this conundrum) - I'm sure this is simple and I'm probably just getting lost in the logic.... So I call upon your collective genius to remind me how...
  13. C

    How to associate an employee name with the name of their supervisor?

    Excel noob here. Each week I have multiple spreadsheets where my sole task is to alphabetize them by employee name and to manually insert their supervisors name next to them. This is incredibly time consuming for me. How can I make it so entering the employees name will yield their supervisors...
  14. N

    YIELD function giving wrong results

    I want to calculate yield to maturity of 3 bonds. I have uploaded the calculations of those 3 bonds in an excel file below. I am not sure why bond A and bond C is giving wrong result. Bond B is ok. I have checked all formats and everything but can't figure out whats the problem. Here's...
  15. L

    formula question - calculating yield

    i have a spreadsheet calculates the total yield of a portfolio. now i have to figure out how to add investments and have the spreadsheet calculate the new yield automatically. what functions should I use?
  16. B

    Duration of Uneven Cash Flows

    Hi all, Is there a way to calculate the duration of uneven cash flows? I realize the duration function will do the trick on a regular bond, but I need something else. The XIRR function is extremely helpful for this when it comes to yields, but I couldn't find a similar function for duration...
  17. N

    Calculating YIELD from interest rate and tenor

    I keep coming across bonds having different annual interest rates and different compounding frequencies (quarterly, half yearly and yearly). I know there is a YIELD function, but it requires so many inputs. I was wondering whether we can calculate cummulative yields just from annual interest...
  18. D

    Calculate best product mix - optimize order picking yield

    Hi, It's been some time since my last post on this board, but now I have new challenge. In short: Is there a way in Excel to calculate the optimal product mix, so that as many orders as possible are fulfilled and at the same time, as many picking orders as possible goes above a certain picking...
  19. V

    Bond Valuation using VBA

    Help. I am trying to value a treasury bond with Face Value =1000 Market Price = 960 4 Years until Maturity Coupon Rate = 7% When I enter the below code and try to solve for YTM. I get an error saying "runtime error 6 - overflow" it says it about half way down where "Dif = P - (Cv * SumIt(N...
  20. P

    Function to calculate interest rate

    I'd like to write my own functions to calculate effective yield based on either 30/360 or actual/actual, but the yield function is not what I'm looking for. My known variables are average balance and YTD interest. For the 30/360 function I would divide by the number of months and multiply by...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back